topic Re: DAX - Average of a Distinct Count in DAX Commands and Tips

DAX - Average of a Distinct Count

PeteDTG — Wed, 18 Aug 2021 22:36:32 GMT

Hello,

Looking for some help with a DAX formula. If you see the image below I am taking the distinct count of the employee_code field to obtain the number of employees per month. However, I also need to show the last trailing 12 months as an average of the distinct counts. How would I go about doing this in DAX?

I can get a distinct count for the last 12 months using the formula below, but it needs to be an average and not a count.

Head Count LTM = CALCULATE(DISTINCTCOUNT(PaycomHours[Employee_Code]),DATESINPERIOD ('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

Thank you!!

Re: DAX - Average of a Distinct Count

amitchandak — Thu, 19 Aug 2021 05:49:14 GMT

PeteDTG , Try a measure like

CALCULATE(averageX(values('edw dimDate'[month year]), calculate(DISTINCTCOUNT(PaycomHours[Employee_Code]))),DATESINPERIOD ('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

Re: DAX - Average of a Distinct Count

community_pinki — Thu, 19 Aug 2021 06:43:12 GMT

Hey PeteDTG ,

Please try using this :-

Avg of Head Count LTM = CALCULATE(AVERAGEX(PaycomHours,DISTINCTCOUNT(PaycomHours[Employee_Code])),DATESINPERIOD
('edw dimDate'[Date],MAX ( 'edw dimDate'[Date]),-12,MONTH))

Re: DAX - Average of a Distinct Count

PeteDTG — Thu, 19 Aug 2021 14:23:12 GMT

You are awesome, thank you so much! Didn't think to use AVERAGEX! 🙂

Re: DAX - Average of a Distinct Count

PeteDTG — Thu, 19 Aug 2021 14:23:42 GMT

Thank you so much!! 😁