Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Calculation Help! +Tutor needed.

Hi Community,  You guys have been my savior when I need help, so I am back here to get some help! I work for a hospitality company and currently in the HR department. I am trying to create Turnover...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a measure. 

    Avg HC = 
var _a=CALCULATE(SUM('All Master Team Detail Data'[Headcount]),FILTER(ALL('All Master Team Detail Data'),'All Master Team Detail Data'[properties]=MAX('All Master Team Detail Data'[properties])))
var _b=COUNTROWS(FILTER(ALL('All Master Team Detail Data'),'All Master Team Detail Data'[properties]=MAX('All Master Team Detail Data'[properties])))
return DIVIDE(_a,_b)

     

     

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous , Based on what I got

 

If you need a monthly avg of measure HC

 

Averagex(Values(Date[Month Year]), [HC])

 

Daily Avg

Averagex(Values(Date[Date]), [HC])

 

YTD Avg

 

calculate(Averagex(Values(Date[Month Year]), [HC]) , datesytd('Date'[Date]) )

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello!

My data goes by monthly but I need YTD Average Headcount. For example, I need to add all the headcount from January - September and divide by 9 to get average headcount. Would you be able to write them using provided table below? 

'All Term Data'[Employee Number],  'All Term Data'[Month]

'All Master Team Detail Data'[Headcount], 'All Master Team Detail Data'[Month]

 

Thank you so much for your help