+ Reply to Thread
Results 1 to 8 of 8

Pivot Table Filter On "Difference From"

  1. #1
    Registered User
    Join Date
    09-14-2018
    Location
    Cleveland, OH USA
    MS-Off Ver
    2016
    Posts
    4

    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?
    Thanks in advance,
    blimbert
    Attached Files Attached Files

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    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.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Pivot Table Filter On "Difference From"

    Another option assuming your data contains unique dates would be to add an additional column to your data.
    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    09-14-2018
    Location
    Cleveland, OH USA
    MS-Off Ver
    2016
    Posts
    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?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-14-2018
    Location
    Cleveland, OH USA
    MS-Off Ver
    2016
    Posts
    4

    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. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-14-2018
    Location
    Cleveland, OH USA
    MS-Off Ver
    2016
    Posts
    4

    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
    Last edited by blimbert; 01-22-2020 at 09:57 PM.

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    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.
    Last edited by Olly; 01-23-2020 at 04:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Table..."Grand total chaned to difference"
    By Statz in forum Excel General
    Replies: 2
    Last Post: 06-23-2019, 08:37 AM
  2. [SOLVED] Filter Pivot Table with a fixed value called "TOTAL" in Pivot Field Name
    By trizzo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2015, 12:57 PM
  3. Replies: 2
    Last Post: 08-13-2014, 02:16 PM
  4. Replies: 3
    Last Post: 01-02-2014, 02:15 PM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. Filter report in pivot table with "greater than" and "less than"
    By gygabyte017 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-29-2012, 08:08 AM
  7. Replies: 3
    Last Post: 03-17-2010, 07:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1