Aggregation Issue with different dimensions

[miguel05]

Hello guys!

I am currently building a report where I need to show the total amount of sales in Euros. The dataset that I have, contains the information of the amount in on column, with the corresponding currency that was used to perform the payment, like the following example:

Amount | Currency

110.2 | USD

95 | AUD

190 | GBP

The idea is to represent the total amount in Euros. For that, I added an offline dataset containing the information about the exchange Rates, like the following example:

Currency | Exchange Rate

GBP | 1.19

USD | 1.01

AUD | 0.69

I added this new dataset to my existing story, and linked the dimensions with the column "Currency". Then, I created a new calculation where I simply made: [Amount] * [ExchangeRate].

The calculation seems to work fine, when representing the information on a tabular way with the corresponding Currency. Although, I want to show the full amount in Euros, and the aggregation of the values is not working correctly. If I show the full amount in a numeric point indicator, SAC performs the following calculation:

(110.2 + 95 + 190) * (1.19 + 1.01 + 0.69)

When I want SAC to perform:

(110.2 *1.01) + (95*0.69) + (190*1.19)

I tried to change the aggregation type of the ExchangeRate column from SUM to NONE and the outcome of the aggregation was 0.

Does anyone from here knows how to solve this? I would be very appreciated.

Thanks in advance 🙂

[N1kh1l · Accepted solution]

miguel05

That's the expected behavior in SAC. If you created the Euro currency measure in stories, you will need another measure of type aggregation of type sum and use the Euro measure inside it. If you used an account member with formula change exception aggregation to sum with the dimension on which you want the aggregation. Something like below.

in my example Revenue = Price *Quantity (in your case EURO_Amount = LC Amount * Rate). IN aggregation dimension select the dimensions you want the aggregation to be on.

Ouput: Aggr Revenue is the aggregated measure showing correct aggregation. Revenue measure first aggregates and then performs the calculation.

Please accept /upvote if this helps.

Nikhil

[miguel05 · Accepted solution]

Thank you so much for your answer. Although I am struggling to implement your solution since I am not able to create a new calculation based on the one that I calculated before

I created the measure "Calculated Measure 1" on my story, which is basically the Euro measure that I created before as [Amount] * [ExchangeRate].

When I try to create a new measure using the one that I created before, it appears the following message in red. Do you know how to solve this? Thanks again for all your efforts 🙂

[N1kh1l · Accepted solution]

miguel05

I think you loaded the Rates into a different model and used blending to create the Local Currency* Rates calculation. Aggregation measure is currently not supported for blended measures. I would advise that you load rates in the same model. Also just wondering why did you not use SAC currency conversion feature?

Nikhil

[miguel05 · Accepted solution]

Basically I added the dataset as a model, then I open my story and added the data there where I performed the linking.

I will try to add this data through the model instead of the story then to not generate this blending issue. Thanks again for your help.

I didnt use the SAC Currency feature since I am not aware of it! I will look at that feature if I cant fix this issue.

[N1kh1l · Accepted solution]

miguel05

Ideal way of doing this is through SAC Currency conversion feature. Else just add both the Sales data and rates data to same model. This way you can bypass the blending issue.

Help for currency conversion:

https://blogs.sap.com/2021/12/28/sap-analytics-cloud-currency-conversion-with-conversion-measure-and...

https://blogs.sap.com/2020/08/05/sac-currency-conversion/

https://www.youtube.com/watch?v=OwYYJJCL1II

Nikhil