Forum Discussion
Define Categories
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
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,
2 Replies
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,