Forum Discussion

shermayne123's avatar
shermayne123
Helper I
2 years ago
Solved

Revenue vs Budget using Line and Stacked Column Chart

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...
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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]
)

     

MFelix's avatar
MFelix
Super User
2 years ago

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's avatar
shermayne123
Helper I
2 years ago

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?