Measures on Rows - Here is How I did it - Excelerator BI

Measures on Rows – Here is How I did it

Level: Intermediate

Update: 2019. You can now do this natively in a Matrix, but this is still a good trick that can be used to solve various problems.

You may or may not be aware that it (previously was) 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:

myMeasure=
     SWITCH([Selected Measure],
         1,[Total Sales],
         2,[Total Cost],
         3,[Total Margin],
         4,[Total Invoices]
     )

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

Power BI DAX Book

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:

Selected Measure = MAX(MeasureTable[ID])

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 Supercharge Power BI.

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.

myMeasure = 
      SUMX(MeasureTable,
              SWITCH([Selected Measure],
              1,[Total Sales],
              2,[Total Cost],
              3,[Total Margin],
              4,[Total Invoices]
              )
      )

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

image

Power BI Online Training

21 thoughts on “Measures on Rows – Here is How I did it”

  1. Hi Matt! Thanks for this example. I had to do modify this a little bit because I needed to dynamically change a client status depending on the year I was looking the information, so I needed the rows depending on the value of a measure, and it worked! The SUMX final part didn’t work (I really don’t understand why, but nevermind). I just wanted to thank you for this!

  2. Hi Matt,

    I guess this will not work on live data right?

    This is my current challenge as well. I need to perform several computations thru measures. If I create a matrix, the measures are only applicable to Values field, not in rows or column table,

  3. What are the Performance implications ? Will Switch evaluates all the measures irrespective of conditions and then outputs only one matching condition ?

    1. It’s complicated. The SWITCH function is compiled before it is evaluated. During that compilation phase, all the measures are expanded and a “quick” evaluation is completed to try to pre-validate those lines that are true or false. So it may, or may not have performance implementations. Besides, there is now a switch in a Matrix that you can use to put measures on rows

      1. Thanks Matt. Yup I observed that in my measures and seems I need to rewrite my time intelligence switch to improve performance. Is this behaviour improved in SSAS 2019?

  4. This workaround is no longer required, the Matrix layout now have under format for Values “show on row” that does this without coding

  5. great post, the new Matrix always Values on ‘rows’. Does this new feature solve this problem, or did you solve a different problem. thanks

  6. You created a Table so you are not using a measure in your row matrix field but the table you created. any way of using the measure directly working live on analysis server does not have the option of creating tables or calculated fields

  7. 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

  8. 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.

    1. 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]
      )
      )

    2. 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.

  9. 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?

    1. Hi Wyn

      Yes I was doing P&L work when I came up with the idea. These 2 approaches will work of course, but I think I would build a SUMX so the totals add up. In my testing, it was all pretty performant.

Leave a Comment

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

Scroll to Top