Forum Discussion

yellow43's avatar
yellow43
Icon for Helper I rankHelper I
3 years ago

Static Average per period excluding values

Hello Community, 

I need help to create an average measure per period excluding a value in a specific column.

This average needs to remain the same despite slicer applied. 

 

This is my data: 

 

DateAgentSales
01/02/2023A2
01/02/2023B5
01/02/2023C2
01/02/2023D3
02/02/2023B2
03/02/2023A5
03/02/2023B2
03/02/2023C1
03/02/2023D3
03/02/2023E4
04/02/2023A1
04/02/2023B1
04/02/2023C3
04/02/2023D2
04/02/2023E4
01/03/2023A1
01/03/2023B2
02/03/2023A4
02/03/2023B2
02/03/2023C3
02/03/2023D2
02/03/2023E3
03/02/2023A6
03/02/2023B5
03/02/2023C6
03/02/2023D7
04/03/2023A2
04/03/2023B1

Average for Feb month is 8 (without Agent "E")

Average for Mar month is 8.25 (without Agent "E")

 

I have tried this approach but it's not working: 

 

Static AVG = CALCULATE(AVERAGE(
'Table'[Sales]), FILTER(ALL('Table'[Date]), CALCULATE(DISTINCTCOUNT('Table'[Agent ]), 'Table'[Agent]<>"E"&&'Table'[Agent]<>"F")))

 

I will always need to exclude Agent "E" and "F" in the measure. And I will need to apply this measure to a Column and Line Combination Chart and to apply a Slicer to Agent. And when I slice Agent "A", the Static AVG should always remain the same for month Feb = 8 and March = 8.25. 

 

Can you help me?

Thank you.  

 

Need Help

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Static Average =
CALCULATE (
    AVERAGE ( 'Table'[Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Date] ),
    NOT 'Table'[Agent] IN { "E", "F" }
)
    • yellow43's avatar
      yellow43
      Icon for Helper I rankHelper I
      3 years ago

      Thank you johnt75 but it's not the desired output. 

      The average should be in numerator = sum sales, denominator = distinctcount agent (without E and F). 

       

      Line and column graphic: 

      x-axis = week

      y-axis column = by agent

      y-axis line = average team (measure above)

       

      Each week I should have a different average by team 

       

      Can you help me? 

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        How about

        Static Average =
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Sales] ), DISTINCTCOUNT ( 'Table'[Agent] ) ),
    ALLEXCEPT ( 'Table', 'Table'[Date] ),
    NOT 'Table'[Agent] IN { "E", "F" }
)