cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaaeson
Regular Visitor

Line and Stacked Column Chart - Totalised scheduled production hours vs a capacity line

I have a production plan table with hours required per product per line per week.

I want the hours per product as stacked columns for each week and the line as a slicer (this I can do).

Where I come unstuck is that I want to use the line series to show the total capacity available vs those stacked product hours bars.

The available hours per line per week comes from a separate table. When I try to combine the production plan required hours with the available hours I end up getting the available hours each week added for every product in the week. I've tried various ways of relating the tables and using DAX to calculate/combine but I can't get it to behave as required when slicing for different production lines/combinations.

 

Production table

WeekProductLineRequired Hrs
1AppleA20
1PearA30
1PinappleB50
1CarrotB60
1GingerC90
1SausageC80
2AppleA10
2PearA70
2PinappleB40
2CarrotB30
2GingerC80
2SausageC90
3AppleA50
3PearA30
3PinappleB40
3CarrotB60
3GingerC30
3SausageC90

 

Capacity Table

WeekLineAvailable Hrs
1A80
1B90
1C100
2A80
2B100
2C100
3A80
3B80
3C80

 

Desired output

Line B.png

Line A.png

Line AB.png

   

 

1 ACCEPTED SOLUTION

Unfortunately, I am still not 100% sure I understand your problem but can you see if tweaking the model in the manner given will resolve the issue. Remember to use the Line and Week in any visual / tables from the Dimension (Line and Weeks) tables and not from the Fact (Production and Capacity table)

 

moizsherwani_0-1632085401760.png

 

 

 

View solution in original post

6 REPLIES 6
moizsherwani
Helper V
Helper V

The desired output is not clear, do you want to show two bars for each week, one which shows the production and one which shows the capacity?

Thanks for seeking clarification - for each week (1,2,3 in this case) I would like a stacked bar of required hours for all products in that week. The capacity is then shown as a separate line series. Here's a chart with the full dataset that I have working in PowerBI - where it falls apart is that it adds together the capacity value for every product, rather than taking one value per line. I can get a workable result for one line by changing to use the average, but that doesn't work when I slice for multiple lines at the same time, because in that case I need the two different capacities added. In the example below you can see the capacity line is below the required hours in the first half of the chart (shortfall of capacity) and then above in the second half (excess capacity).

 

jaaeson_0-1631991693263.png

jaaeson_1-1631992166617.png

 

 

Currently I get capacity by looking up the hours for a given line in a given week from the capacity table. LineWk is just the line and the week concatenated to use as a lookup field.

LineWkHrs = LOOKUPVALUE(Capacity[Available Hours],Capacity[LineWk],'Extract'[LineWk])

 

And then in the production table I have a mesage that averages to workaround the duplication of avalable hours for every product, that is what is plotted via the "line values" optoin in the visualisation.

Capacity = CALCULATE(Average('Production'[LineWkHrs]))

 

I expect I have well over complicated this and gone off down a rabbit hole...

I think I can just append my capacity and production tables and then work directly on that, although there's versions of plans in the production one that add back some complexity...

Can you please share a snippet of "Model" view (as this may be where the fix needs to happen)?

jaaeson_1-1632023107906.png

 

Thanks for persevering!

 

  • The production table has various values per material per receipt wk. (i.e. product per week)
  • The Total Receipts is divided by the rate (ctn/hr) for the material in the Lookups table to get the required hours for that material that week. (as of this snapshot I have the information calculated in PowerQuery and stored as the PrdnHrs column).
  • The capacity table has values of available capacity per line per week.
  • Lookups table is joined to Production by material ID.
  • Capacity table is joined to Production by LineWk

I think the crux of the problem is I don't know how to prevent the capacity figures from being combined for every instance of a material in the production table. I want a stack of total production that I can have sliced by various product groupings from the lookup table and have that showing against a capacity figure that only combines for every instance of "Line".

Unfortunately, I am still not 100% sure I understand your problem but can you see if tweaking the model in the manner given will resolve the issue. Remember to use the Line and Week in any visual / tables from the Dimension (Line and Weeks) tables and not from the Fact (Production and Capacity table)

 

moizsherwani_0-1632085401760.png

 

 

 

View solution in original post

Yes, I needed to use the weeks/line from the dimention table. So simple after going down a rabbit whole of DAX formulae...  Well, I've learnt a lot of new tricks and you've taught me a key fundamental. Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.