Efficiency percentage from multiple criteria.

[A-Aron]

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

[SpartaBI]

@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?

[A-Aron]

its code that i have tried but doesnt work. 

[SpartaBI]

@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?

[A-Aron]

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 

[SpartaBI]

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

[A-Aron]

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. 

[changqing · Accepted solution]

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

[A-Aron]

This worked thank you very much!!

[SpartaBI]

@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