Hi, I am looking for some help with a pivot table I am trying to put together. I am looking to track week on week changes. I thought this would be simple put it is proving impossible. What I am attempting to do is show the figures as + or - in terms of how they have increased or decreased. For example if week 3's figures are 100 and week 4's figures are 150 then the figure will be 50 or +50. I can do this by (Show Values As) (Difference From...). What I then want to do is sort the numbers to show the highest change at the top. Now I thought this would be simple sort largest to smallest. However, I receive a message saying "Autosort and AutoShow can't be used with custom calculations that use positional references. Dyou want to turn off AutoSort/AutoShow?" I only want to see the changes from week 3 to week 4 and I only want to see the top 10 biggest changes. The data I'm working with has lots of lines. I believe there may be a way through "Fields, Items & Sets" but I cannot get any calculation to work. Please help. I have attached a picture of the screen to show what I mean

Pivot Tables - Microsoft Q&A

Answer accepted by question author

Anonymous

Yes, what you want to do is possible. But it is "next level". You need to use measures to calculate the values to display. It's still beyond me, but I have some references that may help you achieve what you want to do.

This site has some really good advice, just look for the "right" pattern to work from.

DAX Patterns web site 2016 – 2019

https://www.daxpatterns.com/
*A pattern is a general, reusable solution to a commonly occurring business problem.*For example, you can use the Ranking pattern if you have sales data in Power BI or Excel and want to know the ranking position of the products you have sold so far.
The most popular patterns are about time-related calculations.
Time patterns are useful to calculate year-to-date values in your Power BI reports, to compare amounts over different periods of time, to calculate a rolling average over 12 months in Excel worksheets, and more.

This is month on month, tweak it to be week on week:

PivotTable Month Comparison 2013 03 02 b Mynda Treacy
https://www.myonlinetraininghub.com/excel-pivottable-month-comparison-video
I’m going to show you how to include the month on month change. In the example below we can see that beverage sales in Feb were $10,391 less than the previous month, and in March they were $4,622 less than February:
.

Group weeks- Group Sales by Weeks With Excel Pivot Tables
https://www.myexcelonline.com/blog/group-date-excel-pivot-tables/
I am sure that your boss has asked you to come up with Total Sales numbers per week at least once in your current role! If not, he will….very very soon! Now your data has only sales for each individual day, which makes you scramble to come up with a solution. How will you be able to group them together and get the sales report your boss needs?
ET PT PivotTables Power Query BI.docx
.
Group Sales by Weeks Starting on a Monday With Excel Pivot Tables
https://www.myexcelonline.com/blog/group-sales-weeks-starting-monday-excel-pivot-tables/
Last time we learned about Grouping Sales by Weeks using Pivot Tables. But what if your boss wanted all of the weeks to start on a Monday? You would be scrambling to find a way to ensure all weeks start on a Monday!
But here’s the thing, setting it to start on Mondays is very easy!
In the example below I show you how to get the Sales Grouped by Date.
ET PT PivotTables Power Query BI.docx .

Google is your friend, here is a search string that found lots of articles on the topic:

https://www.google.com/search?q=excel+pivot+table+week+over+week+change&iflsig=AINFCbYAAAAAYIq2hjxFT89J88Zg1oGGmeMJEqYQEYe6

0 comments

Share via

Pivot Tables

1 additional answer