Overlapping Column Charts...?

[tschmidt87]

Hey,

i want to create 2 clustered column charts overlaid on top of each other (i know, not ideal but currently the only way i can accomplish this visual) to visualize FORECASTED revenue vs ACTUAL (current) revenue

Typically, the actual is on top of the forecasted, to help users understand where their market currently stands (colored columns), and where it needs to be (grey columns), on a regular basis. Since the actual revenue will pretty much always be significantly smaller than the forecasted revenue, the actual graph's y-axis will have a much smaller maximum y-axis limit than the forecasted one.

This results in the two graphs having two different y-axis values and grids. I fixed this by setting the same max. value for the y-axis for both graphs.

The issue is, as you can imagine: every market has ENTIRELY different revenue ranges (from 1500/month to 2.5m/month) so as soon as the user activates the filter for one of their smaller markets, the graph gets rendered somewhat useless as everything is shown in the 0.0M € to 0.1M€ range barely visible at the bottom of the graph.

Unfortunately it doesn't let me apply 'Log' as a scale type for the y-axis for some reason, so im stuck with only Linear. Anyone know how to change this, or how else i could visualize what im trying to do?

Below is an example of what i am talking about:

Left side is the graph when filtered for a bigger market (UK) and right side is the same graph filtered for a smaller market (PT)

[v-qiuyu-msft · Accepted solution]

Hi @tschmidt87,

According to your description, you have created two charts, one contains grey color columns and the other contains colorful columns, right?

In your scenario, the Y-axis Start and End values can't be specified based on the maximum value of current chart values. As you have set the fixed value for both Start and End property, the Y axis coordinate will not change based on the chart values. To work around the issue, I would suggest you create a Line and Clustered Column chart, set Column values as colorful column values, and lines values as original grey column values. Set both Start and End property Auto for Y-axis(column) and Y-axis(Line).

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[nursuhana]

hi Qiuyun,

I need help if you don't mind. I need for future months to show the forecasted (latest estimates) numbers in different colour...

FYI red bar is Actuals, yellow line is plan

[tschmidt87]

Although this approach does not visualize it the same way, it kind of gets the job done. Thanks for the tip Qiuyun 🙂