Skip to Content
Aug 22 at 05:37 PM

Aggregation Issue with different dimensions

141 Views Last edit Aug 22 at 05:38 PM 2 rev

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 :)