Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |