 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. 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. ## 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. 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:  ### 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. ### 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. ### 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. 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.  ### 29 thoughts on “Measures on Rows – Here is How I did it”

1. Hi Matt

I’ve used this idea with great success in a custom matrix. Thank you very much.

What I would like to do next is, if Total Sales is greater than Total Cost then show the Sales value in Green and the Cost value in Red and so on.

I’ve created a third Switch measure to apply the font colours and applied it using the Field Value option under Conditional Formatting but this changes the font colours for all the values and not individually.

Thank you.

1. Nevermind, I solved it.

Format Table =
SWITCH([Switch Measure],
[Sales],
SWITCH(TRUE(),
[Sales] > [Cost], “#3CB371”, “#FF6347”
),
[Cost],
SWITCH(TRUE(),
[Cost] > [Sales], “#3CB371”, “#FF6347”
),
“#FF6347”
)

1. you’re a life saver Yoshi! I never thought of nesting more switch statements for the solution.

2. hello Matt,

I am using exact the same method, but right now I have a requirement to use conditional formatting on one of the columns only for example I want to see colored font only on clothing category. Do you have any recipe for such an requirement?

3. Hello!
Thank you!!! Great alternative, really useful.

Could you help me?
I’m putting Grand Total in Matrix, but not is adding values, resulting in 0.

_BSC =

VAR Crisis = IF(CALCULATE(SUM(‘CrisesSev0&1′[Contagem]))>30;0;1)
VAR CrisisbyChange = IF(CALCULATE(SUM(MudancasCrises[Contagem])>2);0;1)

return
SUMX(PainelBSC;
SWITCH([_Selected Measure];
2;Crisis;
3;CrisisbyChange
))

4. Hi Matt Allington,
Great, Thank you very much.
I was struggling for this and i got the job done.

And i stucked up in oneline item i.e., Measure Rows i have percentage which i require in the format as

Occupancy % – 84%, 90%, like wise
GOP% – 50.2%, 49.20% likewise
Net Profit %. same as above.

Would be highly thankful and appreciated if you could resolve this issue as well

1. It can’t be done. See my comments towards the bottom of my article above and vote for the idea at the link I provided

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

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

1. What do you mean “live data”. Yes, measures only are applicable to values. You can put measures on rows now with a setting in the matrix.

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

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

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

1. Yes, the core issue is now fixed in the updated Matrix. But this technique is still useful for doing things like creating a P&L that will auto fill into a matrix.

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

11. José Ricardo Fonyat

Hello Matt!
Is it possible to insert differents number formats inside the Switch formula?

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

1. Think I’ve worked it out…

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

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

1. Arh, of course yes, but a brilliant idea you came up with.

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

1. Thanks Matt, keep up the great posts

Scroll to Top