So, you have read the title, and are now thinking - is creating multi-level columns in a PowerBI such a big hassle? You would be surprised that in some cases it just is, and how much you can achieve with calculation groups!
Source: own / Movie: Star Wars Episode 3: Revenge of the Sith.
Most of the time you would not be even using calculation groups - just grab a measure, drop it as a value in the matrix, add a couple of dimensions as your columns and/or rows, and voila!
Visualisation pane: Most likely setup for your multi-level column matrix
Requirements - just like Excel
A recent project where the customer has requested a matrix to be set up just like one they have build in Excel really pushed my imagination to how far you can take the matrix visual. Their initial requirement was to re-create this:
Customer’s Excel matrix
Data model
The PowerBI report has already been built, with a complex star schema data model, and multiple ETL pipelines were powering it. The underlying-back end has been built around the idea that each fact would have a baseline, promotional, and incremental values of a metric - so for most metrics there would be 3 columns.
Underlying fact table
In the report we would build the measures in a similar way, as these would be separately displayed in the visuals - so we ended up with:
Base Units
Promo Units
Incremental Units
Base Metric A
etc.
Solution
Part 1/2 - One level of columns
Because of the data model-lock in, I could not unpivot the fact table, and then split the former column names into the metric name itself, and then it’s type (base/promo/incremental). I was also keen at re-utilising the 90-or-something measures I already wrote.
What the data model could have been…
And what the solution could have looked like…
It was high time to give the reliable Tabular Editor 2 a spin, and handle this with some calculation groups magic!
First, I needed some dummy measures to become the rows of my matrix. I created one for the Units, and all the other metrics - and dropped them in as the values of my matrix. After all, PowerBI does not allow numeric measures to be used as rows or columns in this visual.
Metric A = 0
Metric B = 0
Units = 0
Dummies in place.
Once that was done, all I needed was to hit the Switch values to rows toggle in the Values setting of the visual to have them as my rows.
Measures as rows? No problem!
Having that done, I switched to Tabular Editor and created a calculation group called Base/Promo/Incremental with - you guessed it - 3 calculation items under the same names. They will make up the column names.
The DAX expressions for the items are almost identical - what you want to do is construct a SWITCH() statement, which will check for the name of the dummy measure, and then replace it with the actual measure. Rinse and repeat for every calculation item - just swapping the measures in the Promotional group to the ones related to the Promo types of measures, etc.
Base calculation item:
VAR _name =
SELECTEDMEASURENAME () // get the name of the DUMMY measure in the matrix
RETURN
SWITCH (
_name,
"Units", [Base Units], // if dummy's name is Units, replace that value with measure [Base Units]
"Metric A", [Base Metric A], // repeated for all dummy
"Metric B", SUM ( 'Query1'[base_metricB] ), // you can alternatively use in-line DAX to create the calculations
BLANK ()
)
…and so on for for every possible calculation item you might have.
Having done that, I dropped the calculation group into the Columns field of the visual and voila! The dummy values are no more!
Dummy measure values in the rows are replaced by calculation groups.
Part 2/2 - Two+ levels of columns
The customer was delighted - I have delivered a PowerBI solution that worked just like the Excel one! What followed next was another requirement:
Client: Can we also add another level of columns to see both the total amount of a metric, and then have it divided by the units?
Updated Excel matrix
Me:
Source: Star Wars Episode 3: Revenge of the Sith.
Back to Tabular Editor then! Now, I needed to create another calculation group that would also be used as another hierarchy level in the columns, however I had to be careful writing the DAX, as the Per unit expression had to be blank whenever the row was showing just Units. The rest of the rows would always need to be divided by the respective type of units - base, promotional, or incremental.
I would also need a Total column, so I could show just the result of the parent calculation group, which resulted in the following calculation group:
“This is getting out of hand. Now there are two of them!” - source
Total calculation item:
SELECTEDMEASURE () // just the result of the parent expression when used in combination
Per unit calculation item:
VAR _name =
SELECTEDMEASURENAME ()
RETURN
IF ( _name <> "Units", DIVIDE ( SELECTEDMEASURE (), [Units] ), BLANK () )
In essence, all that needed to be done is to check whether the current row is not Units, and if that condition was true, divide the underlying measure by the dummy column [Units].
I then added this calculation group as a hierarchy level below the previous one, and there you have it:
Almost done!
The only thing that was left was to take care of the Name heading in the top left corner - which is the Name column of a calculation group you see and drag to a visual within PowerBI itself. After a quick re-name:
The final solution.
Wrap-up
If you have enjoyed this case study, please make sure to share my Substack, and subscribe, so you and your fellow PowerBI enjoyers can stay up to date as I cover more curious solutions like this!
