Measures on Rows – Here is How I did it

Level: Intermediate

You may or may not be aware that it is not possible to put Measures on rows in a Matrix in Power BI. But I came up with a trick that makes it possible, so read on to find out how.

Measures Can Only be Placed on Columns

First the problem. The only way that you can use the Power BI Matrix visualisation (at this writing) is to place the measures on the columns as shown below.  You can then take any column of data from your data model (typically from a Dimension/Lookup table) and place that on rows in the Matrix.  So you end up with this.

image

This limitation does not exist in an Excel Pivot Table.  As you can see below, it is possible to flip values between columns on rows from the Pivot Table Fields List.

measures on rows

Old tricks for New Purposes

When I was thinking through this problem, my first intuition was to use a Switch statement somehow.  Switch statements can be used to create a Switch Measure (like the ones I used in this blog post here back in 2014) and I figured this could be part of the solution.  And then I remembered another trick I learnt from Rob Collie using disconnected tables to feed a measure – I new I had a solution.

So in summary, I wrote a Switch measure that will morph into different measures when filtered, and then used a disconnected table to pass a filter to that measure.

Write a Switch Measure

The Switch measure is pretty easy to write as follows:

The way this measure works is that it takes an input in the form of an integer, and then depending on that number, it will return the value of the appropriate measure as the result.

Then Create a Helper Table

The next step is the secret sauce to this trick.  I created a disconnected table with the sole purpose of passing the correct filter values (the integers) to the Switch measure.

I used the “Enter Data” feature in Power BI to create a list of the measures I want to put on rows in the Matrix.

image

Note that the ID column is also the sort order of the measures, and the ID column also matches with the Switch measure numbering scheme.  Once the table is created, I added it to the data model as a disconnected table – no need to join it to any other tables in the data model.

I then set the sort order of the Measure column as follows:

image

Write a Harvester Measure

The next step is to write a harvester measure to extract the selected filtered value of the table.  This measure is simply as follows:


The measure above will return the largest integer in the current filter context.  If there is a filter (eg a slicer) on the Measure column and the user as selected “Total Margin” in the slicer, then there is only a single row visible in the filter context, and hence the MAX of the visible rows is 3.  I would of course get the same result if I used MIN, AVERAGE, SUM or indeed even VALUES in my harvester measure.

Filter context is one of the hardest things for new users to understand properly.  I explain filter context in detail (and everything else you need to know to be great at using Power Pivot and Power BI) in my book “Learn to Write DAX”.
L2WD banner ad

You can see the harvester measure in action below.  I have placed the column from the MeasureTable into a slicer and the harvester measure into a Matrix.  When I click on the slicer, the harvester measure updates to show the ID of the selected filter.

slicer

Add the Measure Column to the Matrix

There is more than 1 way to filter a table of course.  In the image above I am using a slicer, but I can also use the rows on the Matrix to provide filter context.  When I place the Measure column from the MeasureTable onto rows in the Matrix, you can see below that the harvester measure is correctly returning the value of the ID column to the visualisation.

image

Pulling it All Together

In order to pull this all together, I replaced the harvester measure with the Switch measure I wrote earlier, and put the Categories column from the Products table onto columns in the Matrix.

image

But there are now 2 problems.  The first problem is that the Total Invoices are shown in currency format.  One limitation of this trick is that all the measures must have the same number formatting.  If you would like to see an optional “Alternative Format” for the SWITCH measure, please vote for that idea here.

The second issue is that the total row is actually showing the total invoices and not the total of all the measures.  This makes sense of course because the grand total row is not filtered.  As a result, the MAX of ID is 4 and hence the SWITCH measure is returning the value of the measure that maps to ID 4 (Total Invoices).

To fix these 2 problems, I removed Total Invoices from my table and rewrote the SWITCH statement to correctly handle the grand total row.

And there you have it, Measures on Rows in a Power BI Matrix.

image

Share?

Comments

  1. Thanks for sharing Matt. There’s a few P&L reports that would benefit from that approach. In this specific example would you turn off Grand Totals or can you suppress them using HASONEVALUE or similar?

  2. Hi Matt,
    It seems to me that the TOTAL line in the matrix doesn’t make much sense since it sums the measures in the MeasureTable ignoring the fact that some are credits others are debits and intermediate results.

    If instead you expand the MeasureTable with an extra column named ‘linekind’ with these codes:

    1 = credits (sales etc.)
    -1 = debits (cost etc.)
    0 = intermediate results

    The MeasureTable would then look like this:

    Measure ID Linekind
    Total sales 1 1
    Total Cost 2 -1
    Total margin 3 0

    You could then use this adjusted measure instead:

    =IF (
    ISFILTERED ( MeasureTable[Measure] ),
    SUMX (
    MeasureTable,
    SWITCH (
    MeasureTable[ID],
    1, [Total Sales],
    2, [Total Cost],
    3, [Total Margin]
    )
    ),
    SUMX (
    MeasureTable,
    SWITCH (
    MeasureTable[ID],
    1, [Total Sales] * MeasureTable[linekind],
    2, [Total Cost] * MeasureTable[linekind],
    3, [Total Margin] * MeasureTable[linekind]
    )
    )
    )

    The TOTAL will then usually show the same amount as shown in the last row if the last row is an intermediate result kind measure, if the last measure is not an intermediate result kind measure the total will show the end result.

    The idea you came up with using a measure table to beat the measure constraint in the Matrix visual is brilliant. Hats off.

    • well, actually the measure can be simplified to this:

      IF (
      ISFILTERED ( MeasureTable[Measure] ),
      SUMX (
      MeasureTable,
      SWITCH (
      MeasureTable[ID],
      1, [Total Sales],
      2, [Total Cost],
      3, [Total Margin]
      )
      ),
      SUMX (
      MeasureTable,
      SWITCH (
      MeasureTable[ID],
      1, [Total Sales] ,
      2, [Total Cost] ,
      3, [Total Margin]
      ) * MeasureTable[linekind]
      )
      )

    • Yes you are correct, it was just supposed to be illustrative. When I deploy such calcs, say in a P&L, I use columns for display sign, calculate sign and also display Y/N as part of the logic.

  3. Hey Matt, great idea.
    Can this approach be used in a SSAS Tabular model? I have managed to get it too work upto the point of getting the actual measure values to display.

    Any ideas?

    Cheers,

    Ian

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x