Calculated field totals over dimension

[wrompf]

Given attached table as an abstract example, following task:

Sales amounts are listed in local currency, now I want to get the totals considering that. So I want to create a calculated field (to display in a KPI visual) that shows the overall total. For that I need to apply a currency transformation formula depending on the country of each row. As I have multiple countries, the correct transformation formula must be applied.

Something like

ifelse
(country =“A”,
sum(sales amount) / formula A,
country = “B”,
sum (sales amount) / formula B

I get errors around nesting aggregated and non aggregated with this approach. What can I do?

[todd.hoffman]

Hi @wrompf- I found some information that might be helpful from an earlier thread:

Aggregated and non aggregated

I have a calculated field that returns a 1 or a 0 depending on a date in a field. (it checks if the date is more current than a hire date and if the field is not null, if both conditions are true, then it outputs a 1 . what i would like to do is then 4 similar fields and divide by another number to determine completion percent. The problem i am running into is the fields are aggregated and wont let me use them in other calculated fields. Is there a way to do this without running afoul?