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 ,

 

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

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's avatar
    MFelix
    Super User
    2 years ago

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

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