6 Replies Latest reply on Aug 16, 2018 4:00 AM by Gaurav Arora

Divide SUM if

Hi everyone,

I added up the value of the Orders for different countries by suing SUM and I plotted the results using horizontal bars.

In my dataset, the majority of the countries use Euro as their currency except for few of them.

What I would like to do, it's to take the sum of one of these non-Euro countries, for example USA, and divide the sum of the Orders value by let's say 1.14 in order to get the corresponding value in Euro in the horizontal bar.

The names of the variables are "Order Total" and "Country".

Is there any possibility to do so?

Alessandro

• 1. Re: Divide SUM if

Hi Alessandro,

As per the previous post the logic should work

If it didnt could you please attach the sample data so that it will be easy to help where the other calculation wrong and what need to be done

BR,

NB

1 of 1 people found this helpful
• 2. Re: Divide SUM if

Hi Alessandro,

A good approach will be to convert all currencies to Euro and then use this new calculated field for your views.

For example, create calculated field [Order Total (Euro)]

IF [Country] = 'USA' THEN [Order Total]/1.14

ELSEIF [Country] = 'United Kingdom' THEN [Order Total]/0.89

ELSEIF [Country] = 'Australia' THEN [Order Total]/1.56

ELSEIF [Country] = 'Japan' THEN [Order Total]/126.02

ELSEIF [Country] = 'South Africa' THEN [Order Total]/16.47

ELSE [Order Total] END

Use [Order Total (Euro)] for your charts and tables.

NB: This will give you the correct result for the current exchange rate conversion. However, if your data will be refreshed on a regularly basis, you will need to constantly change the exchange rates or better still do the conversion in your databse or through an ETL process.

Hope this helps.

Ossai

• 3. Re: Divide SUM if

Hi Alessandro, how is your data set?

Do you have the information about how is Euro countries and non-Euro countries?

Cheers.

• 4. Re: Divide SUM if

I think If could give bad performance.

Much better create a data set with this information and use the union to provide the field to use in the calculated field.

• 5. Re: Divide SUM if

I agree with Ossai. This should help. In case data change frequency is daily then instead of keeping fix values in Calculated field you should add a column of exchange rate in your data table.

• 6. Re: Divide SUM if

Hi,