Average monthwse distinct count

[sadath]

Hi
i have using this DAX to get count of employees

CountStew16:=(calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1)

this is not giving desired result bcoz some 'Empl Code' will be available in Jan only, some will be in Feb..... so on.

the below DAX is giving me correct result. but is there any simpler way??

CountStew16:=(
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=1)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=2)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=3)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=4)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=5)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=6)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=7)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=8)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=9)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=10)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=11)+
calculate(DISTINCTCOUNT('2016Stew'[Empl Code]),'2016Stew'[Days Worked]>=1,'2016Stew'[MONTH]=12)+
)/[CountM]

 

[Ozeroth]

Hi sadath,

Your best bet would be to use AVERAGEX to iterate over months and average the distinct count in each month.

Something like this:

CountStew16 :=
    AVERAGEX (
        VALUES ( '2016Stew'[MONTH] ),
        CALCULATE (
            DISTINCTCOUNT ( '2016Stew'[Empl Code] ),
            '2016Stew'[Days Worked] >= 1
        )
    )

Does this give the same result?

 

[sadath]

Hi
Thanks a lot,