topic Re: Efficiency percentage from multiple criteria. in DAX Commands and Tips

Efficiency percentage from multiple criteria.

A-Aron — Wed, 03 Aug 2022 11:23:54 GMT

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

Re: Efficiency percentage from multiple criteria.

SpartaBI — Wed, 03 Aug 2022 11:34:51 GMT

A-Aron is this the result you want to get?

VAR BR = COUNTROWS(date)

=IF(Location='Brim', BR/12.5) IF(Location='Neo', BR/17)

IF(Location='Vic', BR/14)

What is this code? or it's just a logic?

Re: Efficiency percentage from multiple criteria.

A-Aron — Wed, 03 Aug 2022 11:50:44 GMT

its code that i have tried but doesnt work.

Re: Efficiency percentage from multiple criteria.

SpartaBI — Wed, 03 Aug 2022 11:57:58 GMT

A-Aron can you hard code the results for that column? Didn't fully understand what you want to get.
Also, do you want to this as a calculated column or as a measure to put in table visuals?

Re: Efficiency percentage from multiple criteria.

A-Aron — Wed, 03 Aug 2022 12:05:34 GMT

Im not sure how to go about it. I have the hours they actually worked in a table, and i know what the potential should be but i can't link the potential to the location per row so that the location filter shows the change in potential vs actual. So im trying to take the hours worked in a single day divided by what the potential is to get their efficiency, the problem for me is that they make multiple entries in one day. I figure if i count the entries in one day then divide that count by the potential it will split up the potential evenly over the rows so that itll link to the location to make the filter work. sorry im not great at trying to explain

Re: Efficiency percentage from multiple criteria.

SpartaBI — Wed, 03 Aug 2022 12:23:10 GMT

A-Aron no worries, do you want to show me on a quick zoom call?

Re: Efficiency percentage from multiple criteria.

A-Aron — Wed, 03 Aug 2022 12:33:34 GMT

Im sorry i dont have zoom im on a work laptop so i cant download things. Maybe i can, i have this table where the hours per location is logged. I have the visual as doughnut chart to show efficiency percentage but i would like it to change when i filter the location slicer.

Re: Efficiency percentage from multiple criteria.

SpartaBI — Wed, 03 Aug 2022 12:36:03 GMT

A-Aron I think I myawlf will need to look at it with but maybe someone else here could pick it up from the data provided. Better to open a neq question and delete this one

Re: Efficiency percentage from multiple criteria.

changqing — Tue, 09 Aug 2022 08:07:06 GMT

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 _result

The PBIX file is attached for reference.

PBIX file

Best Regards,
changqing

Re: Efficiency percentage from multiple criteria.

A-Aron — Tue, 09 Aug 2022 14:39:07 GMT

This worked thank you very much!!