Dynamically changing chart axis

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:

1. On this post over at Power Pivot Pro, Idan Cohen has some fancy techniques using MDX.
2. 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