Databars (stacked) linked to excell won't drop to 0

[MaartenLueks]

Dear all,

Currently I'm working on a project in Visio. Some part of the thing I'm designing contains (stacked) databars. I've linked the data (via excel) to the bars. Bar 1 only has one value (and color), bar 2 three values and bar 3 four values. The system works perfectly until the point where the data is updated and the values of the data bars are 0. Bar 1 will drop to the bottom, bar 2 a little bit further while bar 3 stays quite high (values 0 are shown tho). It seems to me this happens randomly (maybe a bug?).

I've tried all possible options I could think of including some programming:

- Try to make the bar 100% transparent when value = 0 (in the shapesheet)
- Programm an IF function which says the bar should drop to 0 when values equal 0 (in the shapesheet).
- Change the values in the excel sheet from 0 to -

The programmed functions are removed when refreshing the data. Furthermore, after the bars contained data one time the 2nd and 3rd bar won't drop to 0 anymore. I have no clue anymore what to do and it simply doesn't look nice when the bars won't drop back to the bottom.

Does anyone have experience with this problem? Is there a (easy) solution? Thanks in advance!!

[Yacine]

Can you upload the file?

[Yacine]

User.BarLength = MAX(0,Prop.msvCalloutField)/User.ValueSum*User.FullBarHeight
When User.ValueSum is 0 you get a devision by zero and the height of the bar cannot be calculated.
Some times it helps switch the view in the shapesheet from formula to values.

[MaartenLueks]

Hey,

Thanks a lot! I works now for returning the bars till 0, when I put an IF function for all the blocks in the stacked bars . However, when I put data in again, the full bar lenght either exceeds the full bar lenght or doesn't reach it. I'm trying stuff out in the shape sheet but so far without luck. Do you have another idea maybe?

Best regards
Maarten

[Yacine]

Hi Maarten,
I think you need to rethink the purpose of the stacked bar.
You want it to have one fixed length and distribute the bars according to their ratio.
What shall the bar look like when all the bars are zero?
Maybe a minimum height could stand for zero??? --> Value of each segment is Max(actualValue, 10^-3)
  So as to never have real zeros?
Then you would end with 6 bars of 10^-3 if all are zero.

Or hide all the bars if the sum is less then a certain value?
You are the one who needs to define this.
Maybe a search in google could tell more on stacked bars?

Questions over questions

[MaartenLueks]

Hey!

Thanks for the help! In the end I found a different solution (maybe less pretty). I've simply added an extra column which stated that if the sum=0, it would be 1 and otherwise 0. After that I just made it invisible et voila. The bars are alway 0 when no data is shown and have the maximum height when at least one value contains data .

Thanks again and I probably will be asking more question in the future 

[daihashi]

Hey!

Thanks for the help! In the end I found a different solution (maybe less pretty). I've simply added an extra column which stated that if the sum=0, it would be 1 and otherwise 0. After that I just made it invisible et voila. The bars are alway 0 when no data is shown and have the maximum height when at least one value contains data .

Thanks again and I probably will be asking more question in the future 

I have many things that are not pretty, but they do exactly what I need them to do.

The great thing about coding is that there are a number of ways to accomplish your goals, and all of them are valid approaches. I use "dummy" cells all the time, similar to what you've done here.