SAC Data Action: Advanced Formula aggregation issue, values summing up

[#1]

Dear Community, i am facing an issue with a SAC Data Action using Advanced Formulas for revenue calculation.

I want to compare two measures with the names "Umsatzmiete_VZ" and "Umsatzmiete" for a list of shops and write the higher value into the target measure "Tatsächlicher Erlös". When using a standard IF-ELSE statement, instead of picking the higher value, SAC evaluates the condition line-by-line across different underlying dimensions (e.g., Profit Centers / Contracts) and ends up **adding/summing up** the values into `Tatsaechlicher_Erloes`.

Code Snippet: 

// 1. Target definition
MEMBERSET [d/Measures] = "Tatsaechlicher_Erloes"
MEMBERSET [d/Datum] = BASEMEMBER([d/Datum].[h/YQM], %Planjahr%)
MEMBERSET [d/Shopbezeichnung] = "Binder Optik GmbH"

// 2. Vectorial IF-ELSE Logic (causes aggregation/summing issue)
IF RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ") >= RESULTLOOKUP([d/Measures] = "Umsatzmiete") THEN
DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete_VZ")
ELSE
DATA() = RESULTLOOKUP([d/Measures] = "Umsatzmiete")
ENDIF

How can I perform a proper aggregated comparison between two measures without running into summing up values improperly?

Any help or best practices would be greatly appreciated!

Best regards, Nico

[vinay_009]

@Hoppeno ,

The summing happens because Advanced Formulas calculates at leaf level for any dimension you haven't explicitly restricted or aggregated (e.g. Profit Center, Contract). So your IF/RESULTLOOKUP comparison isn't running once per shop, it's running separately for every Profit Center/Contract combination, and each of those writes its own result. 

Try fixing it with adding Aggregate_dimensions / Aggregate_writeto - this helps us to aggregate those dimensions before comparing and write the single result to one member instead of each leaf.

Swap in whatever dimensions your model has that aren't already set in a MEMBERSET. If you skip one, it still gets calculated leaf by leaf, causing the same summing issue. Once you aggregate it properly, the comparison works on the totals , so it picks the higher value instead of adding everything together.

Thanks,

Vinay