DAX Measure to calculate Monthly turnover percent

[Kataness]

We have turnover data on a line by line basis, and have merged the headcount for the year/month/business unit/hourly vs. salaried of that person that left (see below).

As above, each record has the total HC for that month, split between Business Unit, Salaried and Hourly, and we need to combine this into an overall headcount for the calculation (leavers that month / headcount that month). How do we retrieve only one instance of each applicable Headcount total for the DAX expression?

Turnover% = 12 *  (DISTINCTCOUNT('ee ID in terms table) / (one instance of the headcount total summed across all sites/months)

And then display this as per below in a monthly line and stacked column chart? Right now the columns are correct, but we can't figure out the y-axis line for the average turnover that month. Hoping this approach allows us to use the other slicers on the page so this visual will update when you flip between Salaried/Hourly, Business Unit and Leave Type etc.

Happy to listen to other methods to achieve the end goal.

Thanks in advance!

[v-rzhou-msft]

Hi @Kataness ,

I think your calcualtion is based on your data model. Please share a sample file with us and show us a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

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

[Kataness]

Suffice to say, this is confidential data so we can't share anything.  We're wanting to see either how to do the calculation through PowerBI, or, if there's an easier/better way to structure the data to do the calculation.  

We have terms info in one query: EE IDs, term date, salary/hourly, business unit and a "Flag" custom column with the breakdown, e.g., 2023-01BUNameSalary.

We have HC info in another query: EE IDs, active month, salary/hourly, business unit and a "Flag" custom column with the same breakdown, e.g., 2023-03BUNameHourly.

These 2 queries are merged via the Flag. 

The bar chart above is accurate/the desired result, breaking the terms down by month and BU.  

The question is for the Line y-axis - how to calculate the turnover percentage at each month?

Turnover% = 12 *  (DISTINCTCOUNT('ee ID in terms table) / (one instance of the headcount total summed across all sites/months)

June’s Turnover % = 12 * The total terms for the current month 58 / the total HC for all BUs/Months 3709 = 18.77%