SAC Calculate after aggregation based on datasphere

[Charlielin]

Dear Expert,

   I am trying to calculate the difference of GP% between current year and last year. But the formula behave kind of weird. Here is our sample data:

We have calculated the GP% for 2024/08 and 2024/07 in the Analytic Model of Datasphere and it worked just fine(Green). But when it come to SAC story to calculate difference of 2 years, instead of simply minus one from the other, it seem to firstly calculate the difference of each SALES/COST/GP(Orange) and then calculate GP/SALES(Red) which is wrong. What we need is the calculation after aggregation which is just simply 0.42 - 0.37. 

Any suggestion?

We are using live connection from SAC to Datasphere, I've tried to create calculated measures both in the view and analytic model but neither of them worked. 

Appendix 1: Using Exception Aggregation for all fields in the Analytic Model

[Savio_Dmello · Accepted solution]

Can you try doing it locally in SAC using Local Formulas in Stories (Table Widgets)

Open your story and select the table widget where you want to apply a local formula.
Click on the table to select it, then access the widget's menu (usually found in the context menu or on the right-hand side).
Add a Local Formula:Go to the "Edit" mode of the table.
Click on the "Add Calculation" option. This is often found in the table's context menu or data panel.
Define the Formula: You will be presented with a formula editor where you can create your local formula. Use the available dimensions, measures, and operators to define your calculation.

[Savio_Dmello]

Use SUM exception aggregation type for GP%, and specify all of the available dimensions as exception aggregation dimensions.

If that does not work, Use SUM exception aggregation type for both GP & GP%, and specify all of the available dimensions as exception aggregation dimensions.

SAP has specifically mentioned about the behavior here 

Aggregations in Formulas | SAP Help Portal

Also check this

Price * Volume Formulas | SAP Help Portal

LMK

-Savio

[Charlielin]

Thanks for your reply. Unfortunately, we are using live connection to Datasphere so exception aggregation cant be done at SAC side.

[XaviPolo]

As you need a calculation AFTER aggregation, it must be done in SAC or in Analytic Model.

Create a Restricted Measure in the Analytics Model for CY and LY, and then create a Calculated Measure with the CY - LY, this will be calculated AFTER aggregation and tranlated as

AGG(X) WHERE CY  - AGG(X) WHERE PY

Regards,

[Savio_Dmello]

In SAC, with a live connection to Datasphere, some calculations might behave differently because SAC delegates most of the calculations to the source (Datasphere in this case), and it may not behave as expected in aggregation contexts.

Try this, Go to your story, select the measure that holds GP%, and create a new calculated measure. In the formula editor, ensure that you're working with aggregated measures. The formula for GP% should be something like: (sum(GP) / sum(SALES))

Then, you can create a new calculated measure to compute the difference between two years:
[GP% for Current Year] - [GP% for Last Year]

If SAC continues calculating GP% incorrectly despite the changes in the story, you might want to pre-calculate the GP% at the aggregated level in the SAP Datasphere model.
In Datasphere, create a calculated column for GP% that aggregates Sales and Costs before calculating the GP%.

[Charlielin]

Thanks for your reply. I've tried to create a new formula in SAC but no luck. The GP% is already pre-calculate in the Analytic Model of Datasphere. I also tried to pre-calculate GP% in the prior level(View) but it turns out even worse, it seem to calculate BEFORE AGGREGATION in the view