Forum Discussion

Katiek's avatar
Katiek
Icon for Helper II rankHelper II
2 years ago
Solved

Calculating Rate of Employee Injuries

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

     

     

  • Katiek's avatar
    Katiek
    Icon for Helper II rankHelper II
    2 years ago

    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