# Pivot Table Filter On "Difference From"

1. ## Pivot Table Filter On "Difference From"

Hello, I cannot figure how to use a pivot table to filter a pivot table by the largest or smallest "difference from" the prior year. The use setting is to see only the top 10 growth or loss versus the previous year.

My sample dataset is as follows:
Year Region Amount
2020 1 100
2019 1 90
2018 1 80
2020 2 80
2019 2 90
2018 2 100
2020 3 100
2019 3 100
2018 3 100

My pivot table calculates the Difference From Amount, using the Previous Year. the result looks like this:
1579617552759.png

What I am trying to accomplish is to filter that Difference From to show things like "Difference From > \$0", or Top 10 Difference From, Bottom 10 Difference From, etc.

Does anyone have any ideas on how to accomplish this?
blimbert

2. ## Re: Pivot Table Filter On "Difference From"

Load your data to the Data Model, add a calendar table, then you can use time intelligence functions to create a difference from prior year measure. You can then filter / sort based on this measure value.

3. ## Re: Pivot Table Filter On "Difference From"

This would be a VLOOKUP (or maybe INDEX(MATCH()) combination depending on your layout which for every date looks up the corresponding amount for the previous year and deducts that from the current date value. Then use the additional column and slicer to filter the PT as necessary.

If the dates are not unique then the formula would need to be modified so that there's no double/triple...etc counting.

4. ## Re: Pivot Table Filter On "Difference From"

Hello Olly, thank you for the response! I am a novice at Data Models, Power Pivot etc, but I read with great interest your response. I have created a calendar table as you suggest, and have created that relationship to my sales data in the data model. In actuality the sales data is a daily billing detail by customer, for the last two years. So, I'm trying to summarize the change in sales for whatever time period the user selects. The calendar table idea is a great addition to this functionality, as it allows the user to pick quarters, months, years, etc.

I am stuck on the part of the solution that you recommend regarding time intelligence functions to create the differences. So, if a user selects Year in the Calendar data, I can get that grouping to function to show the years I the columns. However, I'm just confounded on what to do next to create the measure for the difference? I see how to create the measure in the Pivot table, but am at a loss on the formula and syntax on how to evaluate the period that is selected in the column heading (eg month, quarter, year, etc), and calculate the difference at the customer level.

The next question is likely to be, "Once I have the measure, how do I filter for growth measure +/-\$X?" (since my previous attempt at filtering in the pivot table failed)

I have attached my work thus far (with credit to another post for calculating the calendar date table dynamically).

Thoughts?

5. ## Re: Pivot Table Filter On "Difference From"

Thanks for the response Richard - I initially went down that path, but there are hundreds of thousands of records I the full data set, and a fair amount of replication with multiple dates per customer. The processing overhead was too cumbersome to work well. Thank you for providing your insight!

6. ## Re: Pivot Table Filter On "Difference From"

Great progress, well done.

First step - mark table "qryDates" as the Date Table (PowerPivot editor: Select table "qryDates", select Design > Calendars > Mark as Date Table > Mark as Date Table).

Now create some measures:

``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``
And just for fun:

``Please Login or Register  to view this content.``
Format measures to suit (e.g. Currency, %) and add to your report as required.

A couple of good practice footnotes:

1. Resist the urge to prefix tables and queries, such as tblSales and qryDates. These tables become consumer facing in your data model - use real world sensible names (so, just "Sales" and "Dates" would work fine).
2. Get into the habit of creating explicit measures for everything you want to see as a value in a report, rather than using implicit aggregations of columns. This enable referencing / re-use of all your measures, and can make your model much simpler for consumers.

See attached for example.

7. ## Re: Pivot Table Filter On "Difference From"

Olly, thank you so very much for the thoughtful and detailed explanations! I've learned so much from your guidance, thanks for showing me the path! Although now I realize how little I know about Excel!

I was able to replicate your work, I do have several questions:
1). Most importantly, how does one filter on the "Variance PY" measure? eg, I don't want the full detail, just those +/-\$X,XXX

***Update: I have figured out how to do this in the Filter Value under the dimension column. Works pretty slick!

2). How do I empower the consumer to alter the time periods? Say that they didn't want to compare years, but rather months or quarters? Do they have to manually manipulate the pivot table columns itself? I like the thought of using a Slicer somehow, but in my research I got lost in the "Calculate" function and the Time Pattern functions. eg, I could see have the user pick one time period from a slicer, and use those Time Pattern functions to get the data from the corresponding time period?

***Update:The "Insert Timeline" functionality seems to do an effective job of this. Is that an alternate approach that you may recommend?

3). Your guidance on the prefixing is a good point. You can tell how I've data myself in excel/access - good ideas on the consumer side.

4). I'm not following the second guidance point about "implicit aggregations of columns". Could you please elaborate?

5). Lastly, what is the reason behind the "Mark as date table"? What does that do? I'm imagining it has something to do with the DAX Time Patterns?

Thank you again so much!
Kind regards,
blimbert

8. ## Re: Pivot Table Filter On "Difference From"

1. Simply apply the filter to the row field(s) of the pivot table - Top N, or > x value, as required, based on the relevant measure.

2. There are many options - you could present a date hierarchy (Years > Quarters > Months > Dates) by putting each of these calendar fields in the pivot columns; users can use the +/- buttons to drill up and down and see the required data granularity. You could use slicers to control the overall date scope.

3. There's lots of data modelling good practice you can learn - powerpivotpro.com and sqlbi.com are pretty good places to start.

4. Implicit measures are created when you put a table column into the values section of a pivot table. You can control simple aggregation of that data (e.g. Sum, Min, Max, etc) - but you have not explicitly created a measure. As I mentioned earlier, explicit measures are A Good Thing. This is a good resource to help understand the difference: https://support.office.com/en-gb/art...b-82fd2e5033f4

5. Exactly as you surmise, marking a date table enables the DAX time intelligence functions. There are a few constraints around the Date Table - it must contain full year range of dates which fully encompass all date values in your data model, and must contain a single date field with no duplicate values. All other columns are optional.

Glad you're learning this stuff - Power Pivot completely transforms Excel's abilities.

There are currently 1 users browsing this thread. (0 members and 1 guests)