Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Percentages based Overall Average Headcount By Division

Hi,

I'm trying to get the percentage of those who have been off sick in each Division against the average headcount population for each Division as below.

DivisionAvg HeadcountNo. of Employees STS SickSTS %
Division 1

754

24332%
Division 224721968%
Division 320711053%
Division 42119444
Division 51750518

30%

Division 6600116

19%

Division 7147837726%
Division 8258161024%
Grand Total10054225622%

*STS =  ShortTerm Sick

I've added my measures into a Table visual and the Avg. Headcount figure changes when I select the Business Worker Type (Field/Staff) slicer, where as I need Avg. Headcount to remain as the overall headcount as above.

 

My measures are as follows:

Rolling 12Mth Avg. Headcount =
VAR v_dates = DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH)
RETURN
AVERAGEX(v_dates,[Headcount])
 
Total STS Employees =
CALCULATE (
    DISTINCTCOUNT ( SicknessAbsence[PersonNumber] ),
    USERELATIONSHIP ( Dates[Date], SicknessAbsence[AbsenceStartDate] ),
    FILTER ( SicknessAbsence, SicknessAbsence[STS or LTS] = "STS" )
) + 0
 
STS Emp.% = DIVIDE([Total STS Employees],[Rolling 12Mth Avg. Headcount])
 
 
Is there a way to make the Rolling 12Mth Avg. Headcount measure static when the Business Worker Type slicer is applied?
 
Grateful for any suggestions.
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Managed to solve this via  YT video and adapt the DAX to my model. Video link as below.

    Rolling 12Mth Avg. Headcountv2 = 
VAR  Roll12MthHeadcount = [Rolling 12Mth Avg. Headcount]
VAR IgnoreBWT = 
    CALCULATE(
        [Rolling 12Mth Avg. Headcount],
        REMOVEFILTERS('BusinessWorkerType Lkp'[BusinessWorkerType])
    )
RETURN
 IgnoreBWT

    STS Emp.% = 
VAR Roll12MthAvgHC = [Rolling 12Mth Avg. Headcount]
Var STSSickEmp = [Total STS Employees]
VAR IgnoreBWT = 
    CALCULATE([Rolling 12Mth Avg. Headcount],
    REMOVEFILTERS('BusinessWorkerType Lkp'[BusinessWorkerType])
    )
VAR STSSick = DIVIDE(STSSickEmp, IgnoreBWT)
RETURN
 STSSick

    https://www.youtube.com/watch?v=So6vr3mTHsA&list=PL52omI55z64GpBR9U-0cNp0UBSRt3qfU3&index=33&t=41s 

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi, Anonymous,

     

    How to Get Your Question Answered Quickly

    Since you have not posted any data, image of your data model or a pbix-file, I can only point you in the general direction:

    Rolling 12Mth Avg. Headcount =
VAR v_dates = DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH)
RETURN
AVERAGEX(v_dates,calculate([Headcount],all('Business Worker Type Table'))

     

    but this will depend on your modelling.

     



    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Managed to solve this via  YT video and adapt the DAX to my model. Video link as below.

    Rolling 12Mth Avg. Headcountv2 = 
VAR  Roll12MthHeadcount = [Rolling 12Mth Avg. Headcount]
VAR IgnoreBWT = 
    CALCULATE(
        [Rolling 12Mth Avg. Headcount],
        REMOVEFILTERS('BusinessWorkerType Lkp'[BusinessWorkerType])
    )
RETURN
 IgnoreBWT

    STS Emp.% = 
VAR Roll12MthAvgHC = [Rolling 12Mth Avg. Headcount]
Var STSSickEmp = [Total STS Employees]
VAR IgnoreBWT = 
    CALCULATE([Rolling 12Mth Avg. Headcount],
    REMOVEFILTERS('BusinessWorkerType Lkp'[BusinessWorkerType])
    )
VAR STSSick = DIVIDE(STSSickEmp, IgnoreBWT)
RETURN
 STSSick

    https://www.youtube.com/watch?v=So6vr3mTHsA&list=PL52omI55z64GpBR9U-0cNp0UBSRt3qfU3&index=33&t=41s