Sync Y Axis of 2 Bar graphs by Default

[Anonymous]

Hi,

I have 2 Stacked column charts

1. OP Amount (OP/CP =OP) ,

2.CP Amount (OP/CP =CP)

both are derived from field OP/CP and the measure is value.

I want to sync my OP Amount chart with CP Amount chart, ideally both values in axis are different, OP is higher than CP. but my user is expecting CP graph should be in sync with OP graph to compare OP & CP.

in this case OP has Y axis 0 to 4k but CP has Y Axis 0 to 2k. 

is there a way that we can sync CP chart Y Axis with OP Chart Y Axis (CP y Axis should be 4k but show actual CP Value). I think we can do it maually enterig 4k in Y axis in CP chart but i am looking for dynamic solution and other possiblities.

here I am attaching visuals,

current Visual look:

Expected should be as below, (implemented by changing Y axis for CP manually to 4000)

find the test data screenshot as below used for both the views,

Kindly respond to this and let me know the available options to implement.

Regards

Harsha

[MFelix · Accepted solution]

Hi @Anonymous ,

You cannot make two axys of different visual to syncronized, however you can do the following work around:

• Select the Line and Stacked Column chart
• Create the following measure:

AXIS  SYNC = 
MAXX (
    SUMMARIZE (
        ALL ( 'OP/CP'[Year]; 'OP/CP'[OP/CP] );
        'OP/CP'[OP/CP];
        "Sync"; SUM ( 'OP/CP'[Amount] )
    );
    MAX ( [Sync] )
)

• Setup your chart as previously, and place the AXYS sinc on the line values
• Setting of Y-Axis turn off show secondary
• Shapes and put the stroke width to 0
• Labels turn of label of the measure

If you don't want the name of the Axi sync to show replace it by a dot or a similar thing on the chart itself and make the colour white.

Another option is to hide the legend of the chart and make a new one with a table visual or with some cards.

Result is attach.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @Anonymous ,

You cannot make two axys of different visual to syncronized, however you can do the following work around:

• Select the Line and Stacked Column chart
• Create the following measure:

AXIS  SYNC = 
MAXX (
    SUMMARIZE (
        ALL ( 'OP/CP'[Year]; 'OP/CP'[OP/CP] );
        'OP/CP'[OP/CP];
        "Sync"; SUM ( 'OP/CP'[Amount] )
    );
    MAX ( [Sync] )
)

• Setup your chart as previously, and place the AXYS sinc on the line values
• Setting of Y-Axis turn off show secondary
• Shapes and put the stroke width to 0
• Labels turn of label of the measure

If you don't want the name of the Axi sync to show replace it by a dot or a similar thing on the chart itself and make the colour white.

Another option is to hide the legend of the chart and make a new one with a table visual or with some cards.

Result is attach.

Regards,

MFelix

[Anonymous · Accepted solution]

Hi,

the Sync calculation didn't work well for my requirement with realtime data. The axis in both the sheets went 9k

My requirement is to set CP Amount Axis to OP Amount which is 4k in both places.

but your thought process helped me to think through line & Stacked column chart. Thanks for that.

what I have followed is I ahve derived 2 measure 

1. CP Amount = calculate(sum(amount),op/cp = "CP")

2.OP Amount = calculate(sum(amount),op/cp = "OP")

then in CP Amount chart used CP Amount in Line & Stacked Chart with no line value.

then in OP Amount chart used OP amount in Column value & CP Amount in Line value both are in sync with axis because I have used CP Amount in line value in OP Sheet.

overall your thought process helped me to achieve the requirement. see the below output visuals,

Thank you

Regards

Harsha