Calculate %Overtime worked with SLICER

[leejoel]

Hello,

I need to calculate %OT worked and have that number be dynamic (changing around slicer selection).

I know I could use =SUM(SCHED_OT_HRS) + SUM( UNSCHED_OT_HRS) / SUM TOTAL HRS but this gives me a fixed value over the entire dataset that doesn't dynamically change with a slicer.

Thank you in advance for any help.

WORK_DATE	REG_HRS	SCHED_OT_HRS	UNSCHED_OT_HRS	TOTAL_HRS
1/1/2019	12	0	0	12
1/1/2019	12	0	0	12
1/1/2019	12	0	0	12
1/1/2019	10	0	0	10
1/1/2019	0	0	0	0
1/1/2019	24	0	0	24
1/1/2019	24	0	0	24
1/1/2019	24	0	0	24
1/1/2019	0	0	0	0
1/1/2019	24	0	0	24
1/1/2019	36	0	0	36
1/1/2019	24	0	0	24
1/1/2019	36	0	0	36
1/1/2019	12	0	0	12
1/1/2019	0	0	12	12
1/1/2019	8	0	0	8
1/1/2019	36	0	0	36
1/1/2019	12	0	0	12
1/1/2019	16	0	0	16
1/1/2019	10	0	0	10
1/1/2019	0	0	0	0
1/1/2019	12	0	0	12
1/1/2019	16	0	0	16
1/1/2019	0	0	0	0
1/1/2019	144	0	0	144

[v-yiruan-msft]

Hi @leejoel ,

Which field will be used on slicer options? Could you please check if the following screen shot is your expected result? If yes, please  create slicer with the field "WORK_DATE" first, then create a measure with below formula:

%OT = 
DIVIDE (
    SUM ( 'Labor_Hours_Team_Summary_2020-0'[SCHED_OT_HRS] )
        + SUM ( 'Labor_Hours_Team_Summary_2020-0'[UNSCHED_OT_HRS] ),
    SUM ( 'Labor_Hours_Team_Summary_2020-0'[TOTAL_HRS] )
)

If the above info is not applicable for your scenario,  please provide more detail about your requirement. Thank you.

Best Regards

Rena

[Anonymous]

This is the correct measure:

%OT Worked =
DIVIDE(
	sum( T[sched_ot_hrs] ) + sum( T[unsched_ot_hrs] ),
	sum( T[total_hrs] )
)

and if your data looks like what you've posted, then since all the dates are the same, the measure will also be the same for any selection of dates if 1/1/2019 is among them. If not, the measure results in BLANK.

Best

D

[leejoel]

This did the trick. Thank you very much!

[AllisonKennedy]

@leejoel  What slicers does it need to change with? Does your example use a MEASURE or COLUMN?

Try 

MEAURE = AVERAGEX(WorkTable, (WorkTable[Sch_OT_HRS]+ WorkTable[UNSCHED_OT_HRS]) / WorkTable[TOTAL_HRS])

I suggested AVERAGEX instead of SUMX since you're working with %, but if you give more info we can help better.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[leejoel]

@AllisonKennedyThanks for the reply but that seems to return 'NaN' on every row. My example is currently using COLUMN but I'm open to any suggestions.

The way the line items are broken out sometimes the calculation reverses (OT>REG HR) whereas other times it's normal (REG HR>OT). Could this be why I am getting NaN with your suggestion?

[AllisonKennedy]

You need to do that suggestion as a MEASURE, the X part in the AVERAGEX creates a virtual calculated column for you. 

the NaN is because total hours is sometimes zero, so put that in a DIVIDE function: 

MEASURE = AVERAGEX(WorkTable, DIVIDE(WorkTable[Sch_OT_HRS]+ WorkTable[UNSCHED_OT_HRS]) , WorkTable[TOTAL_HRS]),0))

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[leejoel]

What am I missing?

Measure 2 = AVERAGEX('Labor_Hours_Team_Summary_2020-0', DIVIDE('Labor_Hours_Team_Summary_2020-0'[SCHED_OT_HRS] + 'Labor_Hours_Team_Summary_2020-0'[UNSCHED_OT_HRS]) , 'Labor_Hours_Team_Summary_2020-0'[TOTAL_HRS]),0))

It seems to error toward the end.