Power Bi Measure Percentage Difference between Column total and row total

[Rob_7153]

Hi everyone,
i know there are a variety of posts on this but I couldn't seem to find one that had the solution that I was looking for... I'm hoping one of your wonderful minds can help me out.

Here is my problem: In a Power Bi Matrix, that shows an Avg. per domain for each month, i want to create an additional column that shows the percentage difference between the domain average each month and the Total Avg. of the column for the same month.

For Example the columns D, F, H and J:

Unfortunately i can't find a solution how to get this in my Power Bi matrix. All i can get is the following matrix:

I tried to find a measure or quick measure to get me the desired column, but without success.

Every help on how to fix my problem is much appreciated!


Thanks in advance 🙂


Best Regards,

Rob_7153

[Icey · Accepted solution]

Hi @Rob_7153 ,

A simple sample:

Avg = 
VAR Avg_ =
    AVERAGE ( 'Table'[Value] )
VAR Avg_Domain =
    CALCULATE ( AVERAGE ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Domains] ) )
VAR Avg_Month =
    CALCULATE ( AVERAGE ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Month] ) )
VAR Avg_all =
    CALCULATE (
        AVERAGE ( 'Table'[Value] ),
        ALLSELECTED ( 'Table'[Domains] ),
        ALLSELECTED ( 'Table'[Month] )
    )
RETURN
    SWITCH (
        TRUE (),
        HASONEFILTER ( 'Table'[Month] ) && HASONEFILTER ( 'Table'[Domains] ), Avg_,
        HASONEFILTER ( 'Table'[Month] ), Avg_Domain,
        HASONEFILTER ( 'Table'[Domains] ), Avg_Month,
        Avg_all
    )

Delta = 
VAR Avg_ =
    AVERAGE ( 'Table'[Value] )
VAR Avg_Domain =
    CALCULATE ( AVERAGE ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Domains] ) )
RETURN
    IF ( HASONEFILTER ( 'Table'[Domains] ), Avg_ - Avg_Domain )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rob_7153]

Hi Icey,

thank you for your input and your time!

It works now, you gave me the input that i needed. Since i also have sub-domains i had to adjust the Var Avg-domain code as followed:

Var Avg_Domain= Calculate( AVERAGE ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Domains], 'Table'[Sub-Domains])) 

Have a great day,

Rob_7153