Stacked Bar Chart - Set Limit to A Certain Amount

[RenPBI]

I am trying to visualize data based on a Resource being the Y-axis, Time Allocation being the X-Axis and a Legend which I've set to Priority, but would need to set the maximum value of 70 hours for the Stacked Bar Chart, which would be based on priority (low, medium, high). I would need to display the higher priority allocations first to fill the 70 hours and move to display the lower priority if there's any remaining time. I can display the first part of the visualization fine but where I am struggling is setting the limit based on Priority. I can't figure out how to calculate the limit in DAX and couldn't find any answers on how to limit the Stacked Bar Chart values. Asking for help on it.

[rsbin]

@RenPBI ,

Here is a potential solution for you.  Create 3 new Calculated Columns as follows:

High_Display = SWITCH(
                  TRUE(),
                  [HIGH] >= 70, 70,
                  [HIGH] )

Medium_Display = SWITCH(
                     TRUE(),
                70-[High_Display] >= [Medium], [Medium],
                70-[High] )

Low_Display = SWITCH(
                     TRUE(),
                70-[High_Display]-[Medium_Display] <= 0, 0,
                70-[High_Display] - [Medium_Display] )

Resource	High	Medium	Low	High_Display	Medium_Display	Low_Display
GHI, Y	17	105	59	17	53	0
Ping 123	5	37	285	5	37	28
Busy Guy	18	30	96	18	30	22

Then use these display columns in your Visual.

If your capacity of 70 is going to change, then you can just create a new Measure for [Capacity] and use this in the formulas above.

Hope I interpreted your question correctly and this solution works for you.

Regards,

[RenPBI]

Thank you for this. Since I had to pivot and unpivot my data for the Bar chart, I derived a similar solution during the data transformation using if statements.

Here's what I ended up with after adding an additional layer of priority and changing the hours to 50:

= Table.AddColumn(#"Previous Step", "Critical_1", each if [Critical] = null then 0 else if [Critical] >= 50 then 50 else [Critical])

= Table.AddColumn(#"Derive Critical Display", "High_1", each if [High] = null then 0 else if [High] >= (50-[Critical_1]) then (50-[Critical_1]) else [High])

[Continued on till the lowest level priority]