Forum Discussion

Chris_Ruddick's avatar
Chris_Ruddick
Frequent Visitor
3 years ago
Solved

Multiple filters AVERAGEX - Orders/Month

 

I have the currently existing Measure to calculate the average number of orders per month:

 

AVG Orders/Month =
AVERAGEX(
    KEEPFILTERS(VALUES('Doc Date Calendar'[Month])),
    CALCULATE(DISTINCTCOUNT('UK Sales TOTAL'[Doc Number]))
)
 
I would like to create a new measure calculating this value, but only for 2022 data. Effectively it will require an additional filter using the "Doc Date Calendar [Year]" field.
 
Is anyone able to help with the syntax of that?
 
Thanks in advance
 
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi Chris_Ruddick 

     

    Please try the following:

     

    AVG Orders/Month of 2022 =

CALCULATE(
   AVERAGEX(
       KEEPFILTERS(VALUES('Doc Date Calendar'[Month])),
       CALCULATE(DISTINCTCOUNT('UK Sales TOTAL'[Doc Number]))
   ),
   Filter(ALL(DateTable),DateTable[Year] = 2022)
)

     

    Best regards

    Michael

    -----------------------------------------------------

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

    Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

    -----------------------------------------------------

    LinkedIn

2 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi Chris_Ruddick 

     

    Please try the following:

     

    AVG Orders/Month of 2022 =

CALCULATE(
   AVERAGEX(
       KEEPFILTERS(VALUES('Doc Date Calendar'[Month])),
       CALCULATE(DISTINCTCOUNT('UK Sales TOTAL'[Doc Number]))
   ),
   Filter(ALL(DateTable),DateTable[Year] = 2022)
)

     

    Best regards

    Michael

    -----------------------------------------------------

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

    Appreciate your thumbs up!

    @ me in replies or I'll lose your thread.

    -----------------------------------------------------

    LinkedIn