DAX measure to calculate percentage of capacity hours utilization based on available hours

[Anonymous]

Hello wonderful community, I will try my best to articulate my question.

I have an Excel workbook that stores 2 tables, WorkCentre220 and NZ_Factory_Calendar.

WorkCentre220 Table contains:

1. Date of production order scheduled (Column - 1. Date)

2. Pegged Requirements Quantity in Each units of materials utilized (Column - 2. Pegged Requirement Quantity)

3. Required Hours to complete production order (Column - 3. Required Hours)

4. The Setup Group that the Production Order is assigned to (Column - 4. Setup Group Category)

5. Week Number ( Formula is WEEKNUM(WorkCentre220[1. Date],21 ) Calculated Column

6. Year (Formula is YEAR(WorkCentre220[1. Date] ) Calculated Column

7. Week/Year (Formula is "Week "&WorkCentre220[5. Week Number]&"/52 in "&WorkCentre220[6. Year] ) Calculated Column

NZ_Factory_Calendar tables contains information on Available Number of Machines/day (defaulted at 3/day, value depends on real world situation but lets assume there will be no machine breakdowns until 2030), Number of Production Shifts/day (always 2 shifts/day), % of Hours Utilization (always at 97%) and Available Hours/Shift (always at 7.5 hours/day) from dates between 1st Jan 2022 to 31st Dec 2030. Week/Year column is also available in this table just like table WorkCentre220. NZ_Factory_Calendar also has indication of Work or Holiday which takes into account weekends and public holidays in NZ, I have built in the logic in Excel to return 0 hours in Total Available Production Capacity when it is a Holiday date.

There is one calculated column called "9. Available Capacity" that calculates the Available Capacity per date (per row, as Dates in NZ_Factory_Calendar is primary unique key data) which equals to:

Total Available Production Capacity = Number of Machines/day * Number of Production Shifts/day * % of Hours Utilization (97%) * Available Hours/Shift (7.5 hours) = 43.65 production hours available per day (take this as a guide for daily hours)

I want to be able to create a DAX measure that calculates and indicates underloading or overloading of production planning hours against the Hours Available in Weekly timeframe. Something along the line of:

Production Hours Utilization = ( ABS( 'WorkCentre220'[Required Hours] - 'NZ_Factory_Calendar'[Available Capacity] ) / 'NZ_Factory_Calendar'[Available Capacity] ) * 100%

However this Measure includes 2 columns from different tables and I can't quite figure out how to do this in a resilient way. There will be weeks that my production planning team overloaded the production order and there will be weeks that it will be underloaded. Underloading is fine but we want to be able to capture overloading so that we can shift forward the hours to balance it out. I will receive of weekly extration of this file in Excel format so the production planning hours will be updated in a weekly basis (don't worry about data refresh). I want to be able to have some indication like overloading percentage in RED and underloading in GREEN using a visual that would be of a best practice or a recommended standard. See snapshot below:

I'm quite a beginner in Power BI and DAX so please if anyone can help me, it'll be very much appreciated.

Let me know if you need further clarification. I have included a link that contains the Excel Workbook as the data source as well as the Power BI .pbix file below:

https://1drv.ms/x/s!ApO53FDyjUUO9gKgoAKws3mfm-PS?e=1xCkWk

https://1drv.ms/u/s!ApO53FDyjUUO9gNfxEkuo-bhJgUN?e=wA4N4X

Thanks!

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('NZ_Factory_Calendar'[12. Week/Year])
return
DIVIDE(
ABS(
SUMX(FILTER(ALL(WorkCentre220), 'WorkCentre220'[7. Week/Year]=_select),[3. Required Hours])
-
SUMX(FILTER(ALL('NZ_Factory_Calendar'),'NZ_Factory_Calendar'[12. Week/Year]=_select),[9. Available Capacity])),
SUMX(FILTER(ALL('NZ_Factory_Calendar'),'NZ_Factory_Calendar'[12. Week/Year]=_select),[9. Available Capacity]))

2. Check Meausre – Measure tools -- %.

3. Result:

If you need pbix, please click here.

Work Centre 220 Production Planning Hours.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly