Standard way of display measures in Power BI table visuals (Table and Matrix) – in columns.
But what if you required to display measures in rows and Year/Month in columns to see trend and remain relation with slicers and other visuals, keeping table flexible?
I couldn’t find something like Transpose table in Table/Matrix properties. And it is not possible to pull measure in rows.
In addition, required table contains rows with same value in column [KPI] – “Growth” with indent. And in different rows it has Growth of different measures.
How to do this in Power BI Desktop?
Prepare model – Tables
First of all, we need a skeleton of future table
Important to have column “Order” here as we will use it for sort rule.
I entered Growth with leading spaces to make it look like indent.
My sample model contains only four tables
One with skeleton and remaining three:
Sample data – some KPIs with fake values aggregated on MTD and YTD per Year/Month
Table for slicer (can be omitted)
Prepare model – Relationships
“KPI Trend Table” is disconnected from others
Prepare model – Measures
Let’s define measures that we want to see in resulting trend visual.
In this sample model my measures are very simple
M1 = CALCULATE( SUM(SampleData[Value]), SampleData[Measure] = “M1” )
M2 = CALCULATE( SUM(SampleData[Value]), SampleData[Measure] = “M2” )
However, in real model nothing stops from creation of very complex DAX.
Apply sort rule for KPI column (select column, Modeling -> Sort By Column)
And the main trick – measure that will display figures in resulting table
Finally, we are ready to drag everything to the report page
Add Matrix visual
Add KPI and Unit to rows
Add MMM-YY formatted date to columns + First letter of each month below
Add “Trend Measure” – to values
We can hide MMM-YY (or date) using simple text box with white background and 0% transparency
If end user do not hover mouse he won’t notice that something is hidden.
MMM-YY is important here due to three reasons
- Main – do not aggregate months of different years
- Correct sort of Month Letters, as we have same letters for different months -> We cannot apply sort rule to column with letters.
- More or less the same columns width.
In main measure we work with each KPI separately, so can apply logic for display units, like 1000 = 1K, just need to check value with additional IF statements.
Options to display units automatically is not available in Matrix visual. Even if it would be available – it impacts on entire visual, but we have some measures in %, some in abs., some abs comes from data source already as 1K, some as usual number etc.
So, we have to apply format individually.
Using DAX like this
Usage of variable should help to avoid multiple calculations of measure [M1] during its check in IF statement.
For nice presentation of DAX I use http://www.daxformatter.com/.
File with sample will be available in next post.
Will be glad to know if there is a more simple way to do this with standard visuals 🙂