7 Replies Latest reply on Jul 18, 2016 12:13 AM by ahmed.hasnaoui

Stacked bars filling the "main" bar

Hi all, This should be simple, but I can't get it done: I want a main stacked bar, that shows the budget and other stacked bars inside it that takes place of the main stacked bar. So you can see what you have left. e.g. you have budget, then you have committed budget now you can see your remaining budget. I attached an example workbook, there you can see that the blue budget bar is as high as the budget amount and the other bar gets stacked over it, but I want it to be inside the blue bar, so you can easily see whats left over.

• 1. Re: Stacked bars filling the "main" bar

Hi Ahmed, if you're asking about changing the way the marks are stacked, try Analysis > Stack Marks > Off

• 2. Re: Stacked bars filling the "main" bar

Hi Jeff, thanks this is helpful if you have only 2 measures (like in the example sorry for that), however lets say I have 3 measures. the measures scale always begins from 0, I need it to be stacked but stacked on the previous measure. Is that possible?

• 3. Re: Stacked bars filling the "main" bar

Hi Ahmed, how about adding a Reference Line showing the total budget?

• 4. Re: Stacked bars filling the "main" bar

You could also try dual axes, with Budget on one axis and the other measures on another.  It's a bit more work to construct this, and it's not as obvious when there's no remaining budget, but it's closer to what you requested.

1 of 1 people found this helpful
• 5. Re: Stacked bars filling the "main" bar

Hi:

You will need to have a single Measure (something like "Amount") and an additional Dimension (like Budget Category). Budget Category could have values such as Committed and Remaining. Then you can have a stacked bar chart with Project as the main Dimension (on the x-axis), Sum of Amount on the bars and Budget Category on the Colors Mark. Then each stacked bar will show Committed and Remaining for different project .

• 6. Re: Stacked bars filling the "main" bar

Note this answer was posted before I saw the other responsed

Hi Jeff, thank you but that doesn't help.

I adjusted the sample workbook (attached).

See sheet 1 it is with the idea of reference line. The problem still remains.

E.g. Project A: 5k expensed and 20k committed. The orange bar ends at 20k though. It should stack on the brown bar and end at 25k.

See Sheet 2, I added an open measure (lets say this shows the amount which is open decided by mgmt so we have some buffer ) now it looks like we still have 20k over because of the blue bar is so big in the scale.

Understood my problem? It should be stacked on expensed, commited, and open (where open is the highest) and should so fill the budget bar.

Is that possible doing it without reference lines/bands?

I want it to be like in sheet 3 but without reference bands because of the blue bar being visible in the middle...

• 7. Re: Stacked bars filling the "main" bar

Thats it buddy you're the man
Learned something new, one measure on one axis and measure values on the other, brilliant!