So I’ve been using PowerBi quite a bit lately to build visualization tools, and I’ve noticed while the Drill Down Feature is very helpful, the date hierarchies by default do not always work the way I think they should.
A Quick Example
Basic Column Chart with two Years Displayed
Drilling Into a Year
As you can see, we get the 4 columns of 2016, with a very small Q1, rising through until Q4 is by far the largest value.
This works exactly as I would expect it to, however when I expand down a level my results don’t show as I would expect.
As you can see when I just expand down a level, the data displays 4 quarters again. This time, however, the Q1 number outpaces the Q4 number by a fairly substantial margin. Why is this?
Well the native grouping sees the values of Q1 across multiple years as being part of the same category. Yikes! That’s probably not what you really want to show. You probably need to show Q1 – 2016, Q2 – 2016 etc.
So how to do that?
First I took my Date Table and created a second table using the “Reference” ability in Power BI. From there I grouped everything down by Year & Quarter.
Next I added a column that included a string representation of the Quarter and Year.
Once I have my quarter and year table completed, I returned to my visual, where instead of using the normal date hierarchy I added the Quarterly drill down. I proceeded to do the same thing with the Month and Year until the Drill downs performed the way they were expected to behave (There were some sorting challenges, but on the whole each level was the same as the previous in terms of steps).
Here’s the final chart, with the drill through expanded to the Quarters to show what the result looked like.
