Calculating Rate of Employee Injuries

[Katiek]

Hi All, 

I've created a graph which shows the rate of employee injury per month. The rate is calculated based on the following Measure:

Rate Employee Injury = 

Calculate(([Number Employee Injuries]/130.9)*100).

In this case, in 2023 the number of Full Time Employees = 130.9.

The graph works great, except I have a slicer where I can change it to the year 2022. The rate of the graph is then incorrect because the number of Full Time Employees (FTE) in 2022 is 120.0. 

To work around this I created a table FTE with the following columns:

FTE       Year

130.9    2023

120.0    2022

Now I'm stuck. How do I adjust my original measure to incorporate the FTE Table so it calculates the correct FTE for the year chosen in the slicer. Help please...

Thanks in advance

KatieK

[Katiek]

Hi Dino, 

For some reason my replies aren't showing.

Thank you for your help. I updated the measure as following:

Rate of Employee Injury =

CALCULATE([Number of Employee Injury]/calculate(sum(FTE[FTE]), FILTER(FTE, FTE[Year]=MAX(FTE[Year])))*100). 

This works for 2023. However, again when I switch to 2022, it is calculating the following = (2/130.9)*100=1.53 instead of (2/121)*100=1.65. Does this havw something to do with the use of MAX?

Thanks for your help, 

KatieK

[Anonymous · Accepted solution]

Hi @Katiek 

I'll start by showing you the test data and slicer styles I use:

The test data sheet name is FTE:

You can use the following DAX to create a measure:

Injury Percentage = 
CALCULATE(
    25 / CALCULATE( SUM('FTE'[FTE]), FILTER('FTE', 'FTE'[Year] = MAX('FTE'[Year]))
    ))

I don't know what the [Number Employee Injuries] you give here is, so I'll use 25 instead:

And the final output is shown in the following figure:

Best Regards,

Dino Tao

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