Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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
This did the trick. Thank you very much!
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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?
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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as 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 C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
What am I missing?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |