Skip to Content
Technical Articles
Author's profile photo Charlotte BURNIER

SAP Analytics Cloud Planning : examples of configuration of revenue driver calculations without using If statements in advanced formulas

During a partner workshop, when presenting SAP Analytics Cloud best practices (please refer to one of my previous blogs SAP Analytics Cloud Planning : do you encounter performances issues? What can you do before contacting SAP? | SAP Blogs)

I was challenged by our partners about a use case requiring lots of If statements in advanced formulas.

This blog presents a commonly configured use case with If statements that I suggest replacing with a lighter, easier to maintain configuration.

I would like to take the opportunity of this blog to thank my colleague Christian Schoenbein who, tirelessly, help me elaborating and testing my experiences of configuration, off the beaten track 🙂

Use case

Some of the revenues in Financial Planning aren’t input but calculated, based on other existing data.

For example,

  • Revenue A = driver A1 x driver A2
  • Revenue B = driver B1 x driver B2

My proposal is to define in the model the constants of the calculation for every revenue with two properties, one per member of the calculation.

In my illustration, I tried to use some significant labels for every multiplier.

Option 1 (model with Account dimension)

This model has

  • an Account dimension
  • a generic dimension storing the Metrics required for the calculation of the data of the Revenues,
  • 3 measures: “MetricX” and “MetricY” , corresponding to the 2 drivers, once multiplied, equals to the third measure “Amount”.

This configuration involves an advanced formula that fetches the data stored in the Metrics, with reference to the properties defined in the Account dimension, to allocate the amounts.

In this type of configuration, you don’t have to create If Statements fetching the amounts depending on the Account, you want to calculate, everything is dynamically maintained in the model.

This setting requires that the labels of members of the generic dimension and of the properties of the account dimension are identical.

Please find below some screenshots of the tested configuration.

Account dimension setting

Both properties refer to the details of the calculations with significant labels

In this use case, for example, the “Revenue B2B” equals the “customer average basket items” multiplied by the “items”


Generic dimension setting

This dimension stores the data of the metrics, used in the Account calculation.


Script of the Advanced formula

MEMBERSET [d/Measures] = (“MetricX”, “MetricY”)

MEMBERSET [d/Account] = (“Revenue B2B”, “Revenue B2C”)

MEMBERSET [d/Metrics]=”#”

MEMBERSET [d/Organization]=%Organization%

MEMBERSET [d/Date]=BASEMEMBER([d/Date],%Date%)


Fetching the data of the 2 drivers in using the properties of the Account dimension (in red below)

 DATA([d/Measures]=”MetricX”)=RESULTLOOKUP([d/Measures]=”Amount”,[d/Account]=”Metric “,[d/Metrics]=[d/Account].[p/MetricX])

DATA([d/Measures]=”MetricY”)=RESULTLOOKUP([d/Measures]=”Amount”,[d/Account]=”Metric “,[d/Metrics]=[d/Account].[p/MetricY])

Calculation of the Amount






Option 2 (measure based model without any dimension Account)

Here the same logic applies as in option 1 but without an Account dimension but measures and one Generic dimension that stores everything: dimension members, the input, calculations and the properties to assign the members required for the calculation.



Generic dimension


Script of the Advanced formula

MEMBERSET [d/Driver] = (“Revenue_B2B”,”Revenue_B2C”)


// fill scope from parameters

MEMBERSET [d/Organization] = %TargetOrg%

MEMBERSET [d/Date] = BASEMEMBER([d/Date] , %TargetDate%)


// run copy and calculation

DATA([d/Measures]=”Metric_X”) = RESULTLOOKUP([d/Measures]=”Input”, [d/Driver]=[d/Driver].[p/Metric_X])

DATA([d/Measures]=”Metric_Y”) = RESULTLOOKUP([d/Measures]=”Input”, [d/Driver]=[d/Driver].[p/Metric_Y])

DATA([d/Measures]=”Revenue”) = RESULTLOOKUP([d/Measures]=”Metric_X”) * RESULTLOOKUP([d/Measures]=”Metric_Y”)




Hope this helps 🙂


Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nikhil Anand
      Nikhil Anand

      Charlotte BURNIER

      This is great approach to minimize costly conditional iterative statements. I had followed a similar approach in one of the requirements

      ID Description Metric1 Metric2 Material
      Material1 Material1 100 200 X
      Material2 Material2 150 250 X
      Material3 Material3 120 230 X
      Product1 Product1 Material1
      Product2 Product2 Material1
      Product3 Product3 Material2
      Product4 Product4 Material2
      Product5 Product5 Material3
      Product6 Product6 Material3


      VARIABLEMEMBER #METRIC2 OF [d/Account]   
      IF [d/PRODUCT].[p/Material] = "X" THEN
      DATA([d/Account] = #METRIC1) = ATTRIBUTE([d/PRODUCT].[p/Metric1])
      DATA([d/Account] = #METRIC2) = ATTRIBUTE([d/PRODUCT].[p/Metric2])
      DATA([d/Account]="MetricCalc")=RESULTLOOKUP([d/Account] = #METRIC1,[d/PRODUCT].[p/Material])* RESULTLOOKUP([d/Account] = #METRIC2,[d/PRODUCT].[p/Material])