Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Overall and detailed average

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.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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