Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
A-Aron
Helper I
Helper I

Efficiency 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.   

 

DateLocationHours workedPotential hours 
1/1Brim2

VAR BR = COUNTROWS(date)

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

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

1/1Brim1 
1/1Brim1 
1/1Neo2 
1/1Neo4 
1/1Neo1 
1/1Vic3 
1/1Vic1 
1/1Vic2 

 

1 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

changqing_0-1660032254112.png

The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

View solution in original post

9 REPLIES 9
SpartaBI
Community Champion
Community Champion

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

its code that i have tried but doesnt work. 

SpartaBI
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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

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. 

AAron_0-1659529838426.png

AAron_1-1659529916157.pngAAron_2-1659529937661.png

 

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

changqing_0-1660032254112.png

The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

This worked thank you very much!!

SpartaBI
Community Champion
Community Champion

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.