Forum Discussion

PeteDTG's avatar
PeteDTG
Frequent Visitor
5 years ago
Solved

DAX - Average of a Distinct Count

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 als...
  • amitchandak's avatar
    amitchandak
    5 years ago

    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))

  • community_pinki's avatar
    community_pinki
    5 years ago

    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))

community_pinki's avatar
community_pinki
Icon for Helper II rankHelper II
5 years ago

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))

PeteDTG's avatar
PeteDTG
Frequent Visitor
5 years ago

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