Forum Discussion
Calculated row in a matrix table
Hi,
I'm building my first Power BI dashboard but have spent hours trying to figure out how to do something with little success.
I need to create a calculated row in a matrix table that gives the percentage of one specific row against another specific row.
It needs to be laid out a little like this:
| Region 1 | Region 1 | Region 1 | Region 2 | Region 2 | |
| Country A | Country B | Country C | Country D | Country E |
| Profit | |||||
Costs of Goods | |||||
Overheads | |||||
Interest | |||||
| Tax | |||||
| Tax % |
The calculated row is the Tax % row which is Tax/Profit. No other calculations are needed.
The table comes out perfectly using the matrix tool, but the tax percentage needs to be calculated by Power BI so that it changes every time I drill into something and change the filters etc.
I have managed to create a 'measure' which correctly gives the result but I'm only able to put it in as either an extra set of columns or as a repeating row under every other row rather than just at the bottom.
Any advice would be much appreciated.
Hi Anonymous ,
Please do like this.
Measure = VAR x = SUMX( FILTER( Sheet5, [Column1] = "Profit" ), [Column4] ) VAR y = SUMX( FILTER( Sheet5, [Column1] = "Tax"), [Column4] ) RETURN IF( HASONEFILTER([Column1B]), SUM([Column4]), DIVIDE( y, x, 0 ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Ashish_Mathur
Super User
Hi,
Profit, COGS, Overheads, Interest etc. should all be written as measures as well.
Thanks for the reply.
The problem is that there are a lot more rows than what I've shown and the rows are made up of three levels that I need to be able to drill into and out of. The matrix allows me to do that already but it might get complicated if I try to define everything with a separate measure. I could also have new rows come in at any point after creating the dashboard that the measures wouldn't account for.
v-lionel-msftCommunity Support
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for this!
I plugged it into my dashboard and it almost does exactly what I need to and it's my fault for missing out this bit of info.
I also have multiple layers of rows. It looks like when I have just one level it works fine but bringing in more causes problems. I tried tweaking the measure using 'AND' but I couldn't quite get it to work.
I have three levels of rows that I need to bring into the final matrix that can be expanded and collapsed. Column 1B is an example of an extra layer.
Column1B Column1 Column2 Column3 Column4 Income Revenue R1 A 100 Income Profit R1 B 200 Income Profit R1 C 300 Income Profit R2 D 400 Income Profit R2 E 500 Cost Tax R1 A 600 Cost Tax R1 B 700 Cost Tax R1 C 800 Cost Tax R2 D 900 Cost Tax R2 E 1000 Any chance you could help me tweak it a little to work with the multiple rows too?
Thank you so much again for your help!
- v-lionel-msft
Hi Anonymous ,
Please do like this.
Measure = VAR x = SUMX( FILTER( Sheet5, [Column1] = "Profit" ), [Column4] ) VAR y = SUMX( FILTER( Sheet5, [Column1] = "Tax"), [Column4] ) RETURN IF( HASONEFILTER([Column1B]), SUM([Column4]), DIVIDE( y, x, 0 ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
