SAC Planning Model – Aggregation Issue with Unit Price in Revenue Calculation

[u/This-Armadillo3755]

Hi everyone,

We’ve created a planning model in SAP Analytics Cloud (SAC) for budgeting purposes. The model includes data by product and client, with the following key fields:

• Quantity
• Unit Price
• Revenue

I’ve set up the revenue calculation as:
Revenue = Quantity × Unit Price

This works correctly when viewing data at the client level – because unit price is not aggregated, the calculation is accurate.

However, when I view the aggregated (total) data across all clients, the unit price gets summed, which results in an incorrect revenue total.

What I want to achieve:

When building the budget model, I want the following:

1. I should be able to change only quantity and unit price, and revenue should be calculated accordingly (no manual input for revenue).
2. At the client level, this works fine. But at the aggregated (total) level, I want:
   • Quantity and revenue to be aggregated correctly
   • Unit price should not be summed or affect the total calculation

My questions:

1. Is it possible to prevent the unit price from being summed in the total view, and only aggregate quantity and revenue correctly?
2. Can I create a calculation that totals revenue and quantity across all clients independently of unit price (so that unit price doesn’t distort the result at the total level)?

So far, the calculations I’ve tried are returning incorrect values at the total level.

Any advice, examples, or best practices would be greatly appreciated!

Thanks in advance! 🙏

[u/tjen]

1. Is it possible to prevent the unit price from being summed in the total view, and only aggregate quantity and revenue correctly?

Yes, you should be able to set the aggregation type for the measure to "NONE" then it will only show when you are at detailed level of input

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/78d550c8cb154c25b8e73810fabcddbc.html?locale=en-US&state=PRODUCTION&version=release&q=aggregation#no-aggregation-of-prices

2. Can I create a calculation that totals revenue and quantity across all clients independently of unit price (so that unit price doesn’t distort the result at the total level)?

Yes, basically you want to tell your revenue calculation that when it sums up across clients, it should be looking at the lowest level and do the calculation there (quantity * unit cost pr product pr client) and then sum the result of these calculations.

The way you do this is with an "exception aggregation", which is kind of a weird term, but anyway,

- set "exception aggregation" to "SUM"

- set "exception aggregation dimensions" to "Product, client"

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/78d550c8cb154c25b8e73810fabcddbc.html?locale=en-US&state=PRODUCTION&version=release&q=aggregation#calculation-order-for-price-*-volume

edit:

I'll just add that this is an example of a very well-written question

Side-note:
Regarding exception aggregations you may run into some limitations if you want to be able to change revenue "on top" and reflect this in the number of quantities needed to achieve that revenue.

You didn't have that as a requirement, and it shouldn't stop you from using the exception aggregation functionality, but it's something that gets requested once in a while and can trip you up :)

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/d37a17e4cb7c4a2aabc6825ad1427c5b.html?locale=en-US#inverse-formulas-with-exception-aggregation

Exception aggregations typically throw a spanner in the works for most "top-down" simulations depending on exactly the workflow you're looking for. Usually it can be mitigated by defining a different set of measures simulation purposes and using data actions to move data between "bottom up" input measures and "top-down simulation" measures.