How to organize measures in tables horizontally in Power BI

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)

Calendar

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

1. Main – do not aggregate months of different years
2. Correct sort of Month Letters, as we have same letters for different months -> We cannot apply sort rule to column with letters.
3. More or less the same columns width.

In conclusion

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 🙂