Multiple filters AVERAGEX - Orders/Month

[Chris_Ruddick]

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

[Mikelytics · Accepted solution]

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

[Chris_Ruddick]

That works perfectly, thank you!