I have been sitting on this post for awhile (that looks weird in one word – should that be “a while”? Let’s just skip that discussion). Okay, I got distracted and was wondering how long I was sitting on this… and it looks like this thread over on the Mr Excel forums came back to life about a month ago and got me thinking about it…
The goal is to have charts where the axis/labels/columns/whatever can change via a slicer. So, instead of showing Sales across Years, I can flip and show Sales across Quarters with just a slicer click. Or maybe… flip through {Category, SubCategory, Brand}? I have seen some good work on this problem:
- On this post over at Power Pivot Pro, Idan Cohen has some fancy techniques using MDX.
- BI Consultant, from Germany, Imke Feldmann, also has some good stuff to say (also doing MDX madness) referenced in the forum thread and her post here that is in… uh… totally non-English
We would like to do the same as those fine folks. Because we just can’t get enough of slicing. But, we fear MDX (which… come on, is pretty reasonable, imo). So, how do we get there?
What’s The Problem?
And I don’t mean “why did you crazy boss ask you for this?”. I mean, why is this even remotely challenging? The reason is that measures can only live in the values area.
In a pivot table, you got your rows, your columns, maybe a fllter area… and a values area. The values show the actual numbers, like Total Sales. On the rows/columns, you can drop a column from a table. But you can’t drop a measure (or “calculated field” if you want to support poor naming decisions). Columns, including calculated columns are fundamentally different from measures in that measures are calculated dynamically, at run time, for every single cell in your pivot table (or each value along the axis of your chart). Only columns can be on axis/rows/columns.
Okay, so our pivot rows/columns and chart axis can only have values from a single table column. But we don’t have a single column with both Years and Quarters! So, what oh what can we possible do!? Well, we make one! duh!
Solution
Instead of the years/quarters example let’s do a hierarchy of { Brand, Category, Sub-Category }… because if two levels is interesting, clearing *3* levels is even more interesting! Over to the left you see the relevant part of a model – with a Sales (fact) table and a ProductMaster (lookup table). Over to the right you see a sample of what the ProductMaster might look like.
It would be sinchy (US children slang for “easy” ) to have a graph of total sales by brand or category, but we want to have a single graph that lets a slicer choose flavor we want. And we know that we need all the values on our axis in a single column. So, we are going to make a brand new table. We are crazy like that.
My theory is to use a standard “unpivot” technique. The resulting table is at right and, I don’t find it crazy or surprising, really. I admit that I created this by hand, but … I bet the Unpivot button in Power Query would rock this out pretty well!
I vote we just keep guessing our way through this problem. WHO IS WITH ME!?
Okay, I suspect we are going to want to create a relationship with the new table – cuz that’s just how we roll, ya? The only reasonable relationship we can really make – knowing that relationships are typically one to many is from the new ProductUnpivot (as our “many” side since each Id shows many times) and the old ProductMaster (as our 1 side since each Id only shows up once). So, now our model looks like this:
Okay, so far so good I guess? I feel we are close to… something. Any time we filter the ProductMaster, those filters will flow across to the many side of the relationships… to both the Sales table and the ProductUnpivot table.
We are almost there…
Let’s take a breather and write the simple version of our total sales measure:
Total Sales:=sumx(Sales, Sales[Price] * Sales[Qty])
Well, okay, that killed some time I guess… but it’s time to kick this up to the next level, and since I am confident I can’t actually explain why this works, let me just say that… I looked at the diagram and through “hey, that’s a standard many to many pattern, and I know that if you jam a table name into the measure, it sometimes magically… works. So, that is what I did:
Total Sales:=CALCULATE(sumx(Sales, Sales[Price] * Sales[Qty]), ProductUnpivot)
We have no “direct” relationship between our Sales fact table and this new ProductUnpivot, so we are telling calculate “hey man, be a buddy… and like, include the ProductUnpivot in your filter context”. It… like… magically works.
It doesn’t necessarily feel “natural” to me yet. Somehow the filter has to like… flow “up” from ProductUnpivot to ProductMaster, then filter the Sales rows. And … that is something I am still working to having a proper intuitive feel for. Last time it came up, I was confused then too.
If you want to really dig in, start with these two articles:
http://www.sqlbi.com/articles/autoexist-and-normalization/
http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html
Okay, time for the pretty pictures.
| Simple Pivot with “Type” Slicer and “Brand” Selected | No slicer. Type and Caption on Rows. | Chart with Type Slicer |
 |
 |
 |
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Hi Scott,
this is wonderful: Beautiful & simple = perfect 🙂
Imke
This is mind blowing. The unpivot is completely out-of-the-box thinking. Thanks for the post!
Awesome, Scott. Only thing I would point out is that this would be mind blowingly easy with Power Query IF… the table was originally loaded into the Power Pivot model by Power Query. If it wasn’t, then trying to deal with it is a pain since Power Query can’t be used to read from either Power Pivot or a Pivot Table. If you did load into the model from PQ though, well then you can just reference the existing query from another query, un-pivot and upload. Booyah!
This is actually one of the reasons I’ve taken to going through PQ for all my Power Pivot needs now. Just makes it so much easier to add to the model later. 🙂
Hi Scott,
Great post! If the sales fact table also had a column for Region ID, and that column had a relationship to a Region Master table which included Country / State / City type info, could you somehow use this technique to toggle the axis between all columns on both tables (both Product Master and Region Master)?
It’s all a bit mind boggling.
Cheers
Nick
Now, now… no skipping ahead… 🙂 I plan to take a look at that in a future post.
Hi Scott,
Excellent work. I was grappling this for a while. This is good indeed for a single dimension with multiple attributes.
I am having a challenge to replicate this if I have two dimension tables, one Product and the second one Customer. Measure is the same total sales.
I want to use a slicer to select either Product or Customer and the chart should display total sales by Product or Customer .
Going by your example I would like the Brand Names when I choose Product and Customer Ids when I choose Customer.
Any help would be greatly appreciated.