Synchronizing the secondary y-axis with the primary axis

[ldacey]

I created a Bar+Line graph with the bar on the primary axis and the line on the secondary axis. 

I am displaying the actual metric on the primary y-axis and the forecasted metric on the secondary y-axis. How can I force the secondary axis to be synchronized with the primary?

My issue is that when I drill down into a dimension, the actual performance might show a value like 10, but then the forecasted line graph might be at 15 but it would display lower than the actual value. The cause of this is that the secondary y-axis is scaled differently so it throws out any intuition of actual vs forecast that you would get from glancing at the chart.

[JoaoMNM]

Hi,

Instead of setting a Secondary y-axis, since you want to only use the first one, you only need to add the second y series to the same Y-axis cell.

Kind regards,

JoãoMNM

[KevinGeddert]

I managed to solve this, but I would still like to suggest that a better solution would be for microsoft to prioritze syncing axis the way Tableau does with one button push.

The Problem:

In my case I was attempting to sync a bar chart with a sum of actual values, to a sum of budgets; this was done monthly for a year. So each month you would have an actuals bar, and a budget line that needed to match axis. As a default the two were not aligned. Forcing a data range would fix the initial view, but made cross filtering by groups or GL codes ineffective. Conditional formatting by actuals for both primary and secondary axis would syncornize the two axis but at 1/12 the scale necessary for a calendar chart.

My solution:

You can force the two Y-axis to sync by conditionally formatting them to the same measure. So I created a "Range Set" measure with the sole purpose of correctly managing the Y-axis. I chose to set my range based on actuals so I needed to calculate the maximum monthly actual which can be accomplished with:

Range Set Actual = MAXX(VALUES('Date'[Date]),[Actual])

By setting the conditional formatting to 0 for Minimum and this measure for both the Y and secondary Y axis I was able to get them to match. This does however set your maximum value to exactly the top of your highest value. To create a little more appealing whitespace I added a 20% buffer like this:

Range Set Actual = 1.2*MAXX(VALUES('Date'[Date]),[Actual])

[dmuller]

This seems to stump a lot of people, but the solution is actually really simple. 
Make sure your Secondary Axis values are turned off, switch on the Align Zeros and make sure your min and max ranges are set to Auto. 

You will notice, that if you display the values of the Secondary Axis, the line will move to the logic of those values. But if you leave it switched off and aligned, it will automatically align itself to the primary Y Axis every time. 

[AnthonyH]

Aligning zero's is one thing, but when your secondary axis is quite a ways off from your Y Axis, PowerBI doesnt align the top end either. What this does is creates a graph where the bottom end by 0 is correct, but incorrectly displays the gap between your secondary and the top end of the Y axis.

In this test data, the orange dotted line is a goal. That goal can be dynamic but in this case, its static from month to month. That said, the goal is 925. But because the total proposals for each month are so low, it makes it appear as though they are really close to the goal when in reality, they are not.

This is what it looks like when i synchronize both Axis with the same min and max. Which is great if your data is always between that but if it were to go out of those bounds, now I need something custom and dynamic.

Just pointing this out because the intention of Syncing up 0's is to get close to what Im showing below, but all it does is make sure both graphs start and 0 and not some higher number. In most cases, it will help. But soon as you have a big gap, it starts to lose its effectiveness.

I wish PowerBI had a 1-click feature to align min-max in a dynamic way.

[isaacsa]

For Secondary y-axis, set Minimum/Maximum to "Auto" and turn off "Values".  Then only one axis used for both measures.

[FelipeRamirez]

Thank you @dmuller ! This solved my issue! 😁
Just heads up for the rest, the zoom slider has to be off for both axis.

[Anonymous]

Set the minimum and maximum range for both Y and secondary-Y axis to be the same.  

[isaacsa]

Go to > Format Visuals > Visual > Secondary y-axis >  Turn "On" Values > Then Turn "Off" Values

[rbk1234]

This only hides the values, it does not synchronized them. At first glance it work, but if you compare the values...

[Johannes_Swarts]

This is ridiculous - this issue needs to be fixed!  This function is a single click in Tableau.

[CALCULATEthis]

what...what is this sorcery?

[mrlutton]

There is no option to "Align Zeroes" as far as I can tell in the most recent version of PBI. What am I missing? IWhy is syncrhonizing a seconday y axis so difficult?

[HimanshuDutt]

Go To >>Format>>Visuals>>secondary Y -axis (ensure it is on)>> Align Zeros -- this must be on.

[cpollan]

This synchronizes the bottom of both lines, but not the top. 

One possible solution is the following, but it doesn't work proplery. 

Go To >>Format>>Visuals>>secondary Y -axis (ensure it is on)>> Range >> Maximum >> 'fx' >> Look for the maximum of your graph and choose.

[Ebby]

Reviving again because I found this thread looking for a similar solution.  The visual now has a function option for the maximum point of the Y-Axis, so I created a measure that was the Peak value (with filters) for the selected data and set the maximum point of both the primary and secondary to that Peak value measure and this solved the issue for me.  Now the axis is adaptable to the context filters, is always the same on both line and column, and always has space for the max possible entry. 

[Raubritter]

This is a way to go. You evaluate it doing something like:

MaxAxis =

MAXX(

    SUMMARIZE (

        TableContainingXAxisValues,

        TableContainingXAxisValues[XAxisValues],

        "MeasureForMax", MeasureWhichShoudDefineMaximum

    ),

    [MeasureForMax]

)
Then you go to Y-Axis format, select a formula for Maximum and pick measure MaxAxis. Same can be done for Minumum value. Repeat for the secondary Axis and the task to sync the axes is solved. This way your Max evaluation is fully dynamic and your external filter context is preserved.

[ArifShamim]

There is no function peak value with filters for the selected data which automatically change the axis automatically synronized for both axis. please eloborate further.

[Anonymous]

Hey, since there is no proper solution, here is a workaround:

Drag a copy of Your “Actual metric” (the one you're showing on columns) to line values as well and use Formetting pane "Customize series" option to make it as invisible as possible not to distract. You need also have "Allign zeroes" turned on, and start and end values for axis left blank/auto.

I hope I helped 🙂

[josef78]

Hi,

Also, I need the y-axis to be exactly synchronized.

@Anonymous this workarount works well, but only in cases when line values are smaller then collumns values (in this case is possible add column values also to line part and hide it). But in case when line values are greater or greater/smaller than columns values, I need add line value to collumns part (and it make axis synchronized), but is not possible to hide this collumn (or I don't know how).

After this workaround it`s look:

  But I need hide third (orange) column, which is used only for this workaround). Basically I need show three values: New (as collumn), Finished (as collumn) and Pending (as line) in same y-axis scale.

[Anonymous]

Nothing better than changing the bar colour to the same as background colour is coming to my mind now.