Overall and detailed average

[Anonymous]

Hello,

I'm trying to display the data in a matrix the way I liked but I cannot get the expected presentation.

My report is composed by 4 slicers :

• The name of an expert
• The service
• The channel
• The year

Besides that I use a matrix with the mission type in row and the Average of fees in the values.

I also created a measure used in the matrix, named Overall Average fees, and this is the point not working as expected.

Here is the code of the measure :

= CALCULATE(AVERAGE(mission_exp[Fees closed claims]);ALL(mission_exp[Mission type detail]))

The expected behaviour is the following. I'd like to get the average for all the expert but for each Mission type. What I got with my measure is the average for all the experts and for all the mission type.

If no filters in the slicers are selected I should have the same value in Average and Overall fees.

I hope I'm clear enough and is there any solution to get the expected behaviour ?

Thank you.

[v-shex-msft · Accepted solution]

HI @Anonymous,

All function will ignore filter effect so your formula get similar result on each row.
I'd like to suggest you test with below formula if it suitable for your requirement:

Measrue =
VAR total =
    CALCULATE (
        AVERAGE ( 'mission_exp'[Fees closed claims] );
        ALL ( 'mission_exp' )
    )
RETURN
    IF (
        ISFILTERED ( mission_exp[Mission type detail] );
        ( total + AVERAGE ( 'mission_exp'[Fees closed claims] ) )
            / 2;
        AVERAGE ( 'mission_exp'[Fees closed claims] )
    )

Regards,

Xiaoxin Sheng