The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
I have a complexe matrix with a hierarchy in the matrix line and 2 columns. When I put on Column Total I get 2 Column added : Total 1 and Total 2. Till here every thing is alright. Now I want to show the percentage between my two column totals in my power BI matrix.
Lets say I have a "Total 1" and a "Total 2". How can I do to just add this percentage '%Total' at the end of my matrix ?
The result needed should be Total1/Total2 * 100
I didnt find a way to do that for two reasons :
I didnt find how I can use the Precalculated Column Totals
I didn't find how to add a new measure to my matrix without destroying its shape and display. Here I use only one measure in the value field of my matrix.
Here is my actual matrix. Any help please ?
@Greg_Deckler @V-lianl-msft @v-zhenbw-msft @v-easonf-msft @v-chuncz-msft @v-xuding-msft @v-alq-msft
Hi, @Khalyda
Based on your description, I created data to reproduce your scenario.
Table:
You may create a meausre like below.
Result =
var _name = SELECTEDVALUE('Table'[Name])
return
IF(
NOT(ISFILTERED('Table'[Grade]))&&NOT(ISFILTERED('Table'[Class])),
(
ROUND(
DIVIDE(
CALCULATE(
SUM('Table'[Score]),
FILTER(
ALLSELECTED('Table'),
'Table'[Name] = _name&&
'Table'[Grade] = "g1"
)
),
CALCULATE(
SUM('Table'[Score]),
FILTER(
ALLSELECTED('Table'),
'Table'[Name] = _name&&
'Table'[Grade] = "g2"
)
)
),4
)
*100
)&"%",
SUM('Table'[Score])
)
Then you need to go to 'Visualizations' ribbon, click 'Format'=>'Subtotals', set 'Column subtotals' as on and 'Column subtotals label' as '%Total'.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Thanks for your answer. But I think I forgot to mentione that I am using a Live connexion to a tabular model and the measure I am using now is calculated there. You think I can use your measure in SSAS ?
What you are looking for is the total with filter. But I do not think you can add that ratio to Matix.
I posted an idea on that: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
91 | |
82 | |
64 | |
58 |
User | Count |
---|---|
246 | |
124 | |
115 | |
79 | |
78 |