Percentage between two column totals in a matrix

[Khalyda]

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 

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

[Khalyda]

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 ?

[amitchandak]

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