Define Categories

[harib]

Hi

I have a Department column with diffrent sections. i would like to know the Ratio of attendence of the employees.
Ex : Lets take Department (Administration)

I have created measures and calculated like below to get the ratio

1 I have counted total workingdays count

2 No of attended days count
3 To get the ration divided No of Attended days Count with Total Workingdays Count

Total Workingdays Count = COUNT(Attendence[Shiftdate])

No of Attended days Count = COUNTROWS(FILTER(VAttendence, Attendence[Attendancestatus]="HD"||Attendence[Attendancestatus]="P"||Attendence[Attendancestatus]="WO"))

Compliance Ratio = DIVIDE(Attendence[No of Attended days Count],[Total Workingdays Count])

My requirment is based on the ratio we have to define the category and need count of category. Out put should be like blow image. Source also i have attached.

Categories

Less than 15% -Mild
15% to 30% - Major
More than 30% -Chronic


Source Link : 

https://drive.google.com/file/d/1OKMDTLdBjxz3V6fTs-Asd52tGA_rMK57/view?usp=sharing

Kindly let me kow if any one get the solution.

Thanks
Hari

[v-jiascu-msft · Accepted solution]

Hi Hari,

I created a solution based on your sample data. Please don't share anything confidential here.

Please download the demo from the attachment. 

Measure =
VAR tempTable =
    SUMMARIZE (
        Attendence,
        Attendence[Department],
        'Calendar'[Year],
        'Calendar'[Month],
        "ratio", [Compliance Ratio]
    )
RETURN
    SWITCH (
        MIN ( Categories[Name] ),
        "Mild", COUNTROWS ( FILTER ( tempTable, [ratio] < 0.15 ) ),
        "Major", COUNTROWS ( FILTER ( tempTable, [ratio] >= 0.15 && [ratio] <= 0.3 ) ),
        "Chronic", COUNTROWS ( FILTER ( tempTable, [ratio] > 0.3 ) )
    )

Best Regards,

[harib]

@v-jiascu-msft 

It's working perfectly. Thanks a lot