DAX Calculation Help! +Tutor needed.

[Anonymous]

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 % (YTD) in my Power Bi dashboard and am facing problems with it. It must be a simple calculation because it is as simple as this - Turnover Calc = [(Total Term Count/ Avg HC)*(12/ Completed Months)].
I already have Total Term Count but Avg HC. I have HC but no idea how to create Avg HC.
Let's say I have a table 'All Term Data'[Employee Number] for Total Term Count and 'All Master Team Detail Data'[Employee Number] for Headcount. To calculate Turnover Calc, I need Average of Headcount, not Count of Headcount.
Does anyone know the DAX calculation for this Turnover Calc?

And also if there is anyone who can share their Linkedin for PBI help, please let me know, I would like to contact individually for a tutor or something.

Please let me know if the question is not understandable.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Anonymous · Accepted solution]

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]

@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]

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