To start this blog post, let me first explain the problem. Consider a scenario where you receive an excel sheet or data in the following format
Category | SubCategory |
Bikes | Mountain Bikes |
Road Bikes | |
Touring Bikes | |
Components | Handlebars |
Bottom Brackets | |
Brakes | |
Chains | |
Cranksets | |
Derailleurs | |
Forks | |
Headsets | |
Mountain Frames | |
Pedals | |
Road Frames | |
Saddles | |
Touring Frames | |
Wheels | |
Clothing | Bib-Shorts |
Caps | |
Gloves | |
Jerseys |
In the above case, the data is already grouped by Category. If you try to load this data in PowerPivot Data Model & try to create a Power view Column Chart report on Category, the view would look similar to shown below
Most of the data is grouped in Blank Category since the Power Pivot Data Model doesn’t know or understand that data was already grouped by Category.
To ungroup the data back in PowerPivot Data Model, I would first add an Index column to the existing data which can be easily added when you are loading the data using Power Query. So the Power Pivot Data Model now looks as shown below
Next, we add Calculated Column viz MaxIndex to calculate & store the index value of last non blank value of the Category for each row. The DAX calculation is shown below
=MAXX(FILTER(FILTER(Table1,LEN(Table1[Category])>0),Table1[Index]<=EARLIER(Table1[Index])),Table1[Index])
The output of the above calculation looks like following
The above table is now like a Parent-Child Hierarchy where MaxIndex is parent id. So to ungroup the Category column, we can now use LOOKUPVALUE Function as shown below
=LOOKUPVALUE([Category],[Index],[MaxIndex])
The output of the above calculation is as shown below
We can now hide the Category, MaxIndex & Index Columns from Client tools to make it invisible from Power view. Now if you visualize the same data in Column chart in Power view, the result is what we are looking for
Hope this helps !!!
Parikshit Savjani
Sr. Premier Field Engineer
Hey great post. I hope it’s ok that I shared this on my Facebook, if not, no issues just tell me and
I’ll delete it. Regardless keep up the great
work.