Column chart values do not stay vertical?

[dtartaglia]

Hi,

I have a Line and stacked column chart, when my sliders ar set somtimes I get vertically 0 to 5 as I expect (see below)

...if I change my sliders that set other info in the chart see how 0 is at the same height as 2, 1=3 and 2=4. All I want is to keep the vertical values on the left and above each other so this chart makes sense.

Thanks,

Dan

[dtartaglia]

Hi,

It seems the issue happens when the Line Value (Red horz red line) has a higher value than the Column values. Is there a solution or work-around for this?

Thanks,

Dan

[KGrice]

Hi @dtartaglia. I found a setting that will prevent the secondary Y axis from showing, and fixes the issue. If you click your chart, then click the paintbrush in the visualizations pane, expand the Y-Axis section. Scroll down to the bottom of the Y-Axis section and there's an option for Show secondary. Turn it off and you should be good to go.

[dtartaglia]

Hi @KGrice,

Thanks for the suggestion but that setting was off 😞

From my testing it seems that if the line value has a value higher than the columns this issue happens othrwise the chart appears correct. I'm not sure what's going on. I thought about using DAX to check for this condition then not show the line if it happens but that's a bad work-around.

Thanks,

Dan

[dtartaglia]

I created a very simple example but cannot upload it (let me know if you want it). If you set the Rows slicer to 4 or 5 the issue will be visible. If there isn't a solution for this, can it please be added to the list of improvements? Does someone know a DAX statement I could use in a Measure to set the line to 0 if the Rows value is higher than the highest Column value? The line needs to stay straight.

Thanks,

Dan

[KGrice]

I tried recreating your example and I'm not getting the same output. I even tried setting Rows to 1000, but it still all stays on the same axis if the Show secondary option is turned off:

However, if you're ok using a measure to set Rows to 0 if it is higher than the max value for any column, you can set that up with a few measures:

ValueSum = SUM('Columns'[Values])

HighestColumnValue = CALCULATE(MAXX(VALUES('Columns'[Columns]), [ValueSum]), ALL('Columns'))

Then use this measure for your Line values:

RowsMask = IF(MAX(LineValue[Rows]) > [HighestColumnValue], 0, MAX(LineValue[Rows]))

[dtartaglia]

Hi @KGrice,

Thanks for the DAX, I've been emailing someone at Microsoft who can reproduce my issue. May or may not be resolved.

Thanks again,

Dan

[dtartaglia · Accepted solution]

A Microsoft Developer supplied the answer. There is a somewhat bug in the Show secondary setting.

This setting actually has 3 states and not 2.  By default it appears as OFF but it’s really set to Auto (but looks off). Once you set it on then off again, it stays off.   If in the undefined state, if the variation between numbers on the axis exceeds 10% the auto feature changes the state. Setting it explicitly removes the Auto.

Hopefully this will be resolved in the near future.