Forum Discussion
Helper IEfficiency percentage from multiple criteria.
Hello friends i hope i can explain this clearly,
I have the sample table below that logs work records from multiple combined tables. Each location has hours worked and a potential of hours that i have specified. I need to create a way to calculate an efficiency percentage that filters by site. There are multiple entries per date per site so i need to figure out how to count the multiple days filtered by site then divide the potential hours which is specified by the count of each day. For example; Brim have a total of 12.5 possible hours per day. They logged 3 entries for Date 1/1 for a total of 4 hours. Im trying to get potential hours column to count the amount of entries for date 1/1 then divide 12.5 by how many the count of date is to get 4.1 in each row so i can use for a filter.
| Date | Location | Hours worked | Potential hours |
| 1/1 | Brim | 2 | VAR BR = COUNTROWS(date) =IF(Location='Brim', BR/12.5) IF(Location='Neo', BR/17) IF(Location='Vic', BR/14) |
| 1/1 | Brim | 1 | |
| 1/1 | Brim | 1 | |
| 1/1 | Neo | 2 | |
| 1/1 | Neo | 4 | |
| 1/1 | Neo | 1 | |
| 1/1 | Vic | 3 | |
| 1/1 | Vic | 1 | |
| 1/1 | Vic | 2 |
Hi A-Aron ,
Please new a calculated column:
Potential hours = VAR _countrows = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) && 'Table'[Location] = EARLIER ( 'Table'[Location] ) ) ) VAR _result = SWITCH( 'Table'[Location], "Brim",DIVIDE(12.5,_countrows), "Neo",DIVIDE(17,_countrows), "Vic",DIVIDE(14,_countrows) ) RETURN _resultThe PBIX file is attached for reference.
Best Regards,
changqing
