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

[jaaeson]

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

Week	Product	Line	Required Hrs
1	Apple	A	20
1	Pear	A	30
1	Pinapple	B	50
1	Carrot	B	60
1	Ginger	C	90
1	Sausage	C	80
2	Apple	A	10
2	Pear	A	70
2	Pinapple	B	40
2	Carrot	B	30
2	Ginger	C	80
2	Sausage	C	90
3	Apple	A	50
3	Pear	A	30
3	Pinapple	B	40
3	Carrot	B	60
3	Ginger	C	30
3	Sausage	C	90

Capacity Table

Week	Line	Available Hrs
1	A	80
1	B	90
1	C	100
2	A	80
2	B	100
2	C	100
3	A	80
3	B	80
3	C	80

Desired output

   

[moizsherwani]

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?

[jaaeson]

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

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

[moizsherwani]

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

[jaaeson]

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

[moizsherwani · 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)

[jaaeson]

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!