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 🙂

Advertisements

9 thoughts on “How to organize measures in tables horizontally in Power BI

  1. Alex 2016-11-11 / 13:57

    Very interesting. I’ve used this solution and it suits well! But the order reported in the matrix (dashboard) does not follow the order specified in the KPI Trend Table.

    • Ivan Bondarenko 2016-11-11 / 15:27

      Hi Alex, thanks for comment! Didn’t you forget to apply “Sort By Column” for column with KPI names? It is essential step to keep your custom order, otherwise Matrix visual will sort KPIs alphabetically.

  2. Shalini 2017-02-07 / 14:14

    hi,

    Could you please let me know how you are linking Calendar table and KPI Trend table.

  3. Frank Tonsen 2017-04-21 / 14:57

    Unfortunately, given a real world PBI model the only way to build a data table corresponding to your “Sample data” seems to be manually.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s