Revenue vs Budget using Line and Stacked Column Chart

[shermayne123]

Hi, 

I have created a line and stacked column chart to allow users to see the monthly revenue against the budget, the shortfall or surplus if revenue is below or above budget. My chart looks like this:

So e.g. when user hovers over the bars in Jan, they are supposed to see that total revenue is 1.24m(data label on top of the bar), surplus is 0.1m(green bar) and budget is 1.14m(blue bar). When they hover over the bars in Apr, they will see that revenue is 0.48m(blue bar), shortfall is 0.66m(red bar) and budget is 1.14m(data label on top of the bar). 

My problem is when the revenue is above budget, the blue bar will still show it as monthly revenue, when the actual revenue is blue bar plus green bar and the blue bar is supposed to be the budget in this case. When revenue is below budget, there is no issue for the tooltips to show blue bar as the monthly revenue. Is there any way to make the tool tip for the blue bar dynamic, so it will reflect blue bar as the budget when the revenue is above budget, and as the revenue if it's below budget? Otherwise is there another better way to create the charts? My main requirement is for the revenue, shortfall or surplus to be stacked together with the data labels shown. Please let me know if you need further info. Thank you so much for any help!

[MFelix]

Hi @shermayne123,

How is the calculation for the blue bar done? If you are using different calculations when you over the blue bar you should get only the blue bar value.

To what I can understand you want that the blue bar present the budget correct?

[shermayne123]

Hi Miguel,

Thanks for responding and apologies for the delay.

This is the formula I used for the blue bar: 

Annual Secured = MIN([Total Forecasts],[Annual Tier 2 Budget])

How do I get it to change to Annual Tier 2 Budget if the Total Forecasts is higher than the Annual Tier 2 Budget? In this case, the value is the Annual Tier 2 Budget but it's still showing the variable/measure name as Annual Secured. Thank you.

[MFelix]

Hi @shermayne123 ,

Based on what you wrote you just need name to have different names correct and the values are the ones that should be presented, is this assumption correct?

In this case what I believe you need to do is to have different measures for the different values meaning:

• Actuals Below Target = IF([Total Forecasts] > [Annual Tier 2 Budget],[Annual Tier 2 Budget])
• Until target value = IF([Total Forecasts] > [Annual Tier 2 Budget],[Total Forecasts] - [Annual Tier 2 Budget])
• Target Value = IF([Total Forecasts] <= [Annual Tier 2 Budget],[Total Forecasts])
• Above Target Value = IF([Total Forecasts] <= [Annual Tier 2 Budget], [Annual Tier 2 Budget]-[Total Forecasts] )

This will allow for different colour and different names on your visualization.

[shermayne123]

Hi Miguel,

Thank you for the detailed formulae. 

Yes you got it right that I want the names to show differently for different scenarios.

I've adjusted your formulae for my purpose as follows:

Surplus = IF([Actual]>[Total Tier 2 Budget],[Actual]-[Total Tier 2 Budget])

Shortfall = IF([Actual]<=[Total Tier 2 Budget],[Total Tier 2 Budget]-[Actual])

Actual = SUM([Secured revenue])

So it is working now. But then I noticed that if the actual is above tier 2 budget, how can I set the formula such that it will go on and check tier 3 budget and use tier 3 budget to less off the actuals to derive the surplus? Is there a if-else formula to use? Thank you so much!

[MFelix]

Hi @shermayne123 ,

For that you can use a SWITCH syntax were is the same as concatenated IF statments. would be something similar to:

Surplus = 
SWITCH(
     TRUE(),
     [Actual]>[Total Tier 2 Budget],[Actual]-[Total Tier 2 Budget],
     [Actual]>[Total Tier 3 Budget],[Actual]-[Total Tier 3 Budget],
...
)

[shermayne123]

Hi Miguel,

Thanks for your advice!

With this switch function, how can I make it work for the shortfall from the tiers? I tried the following formula but it computes the difference from the highest tier instead of the nearest tier:

Shortfall = SWITCH(

    TRUE(),

    [Secured]<[Total Tier 4 Budget],[Secured]-[Total Tier 4 Budget],

    [Secured]<[Total Tier 3 Budget],[Secured]-[Total Tier 3 Budget],

    [Secured]<[Total Tier 2 Budget],[Secured]-[Total Tier 2 Budget]

)

How can I add in for the formula to compute if the actuals fall within the range e.g. between tier 2 and tier 3? And I only want it to compute from the nearest tier i.e. if it is higher than tier 2 but below tier 3, the shortfall should be computed based on tier 3 less actuals rather than tier 2. Thank you!

[MFelix · Accepted solution]

Hi @shermayne123 ,

In this case you need to take into account that the switch makes it based on  the order so for this you should do it the other way around start in tier 2 and end on tier 4.

Shortfall = SWITCH(
    TRUE(),
    [Secured]<[Total Tier 2 Budget],[Secured]-[Total Tier 2 Budget],
    [Secured]<[Total Tier 3 Budget],[Secured]-[Total Tier 3 Budget],
    [Secured]<[Total Tier 4 Budget],[Secured]-[Total Tier 4 Budget]
)

[shermayne123]

Hi Miguel,

Thanks so much for pointing out the correct way to compute the shortfall. Now I am able to present this results in a matrix table visual.

I've one related question i.e. if I want to present this in a stacked column chart format, how can I get the shortfall to be stacked on top of the actuals instead of being in the negative zone under the y-axis? And is it possible to have the nearest tier budget to be presented as a line/marker in the stacked column?