convert category in local currency into EUR with a specific category rate

[Nun]

Hello,

please can someone support to solve this issue?

I have a table

Sales Lc SalesEUR Category Currency code

100        10            Pre          ARS

200        20            Pre          ARS

500       70             After       ARS

250      40             After        ARS

350    100             After        BRL

500   250              Pre          BRL

I need to create a measure where the currency rate is calculated based a "Pre" category (if we consider the currency code = "ARS", the currency rate is 300/30 =10), but appied for the category "After" and Sales Lc. In pratic once is defined the currency rate, moltiply for the column "Sales Lc" which the category = "After".

the result of the measure, in case of currency code ARS is 750(Sales LC with category "After")/10 (currency rate calculated based on sales LC with category "Pre")= 75

for Currency BRL, for example, should me a measure 350/2,5

Thanks!

[ryan_mayu · Accepted solution]

@Nun 

if you have a filter of category, you can try this

Measure = 
VAR a=CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Currency code]),'Table'[Category]="Pre"))/CALCULATE(SUM('Table'[SalesEUR]),FILTER(ALLEXCEPT('Table','Table'[Currency code]),'Table'[Category]="Pre"))
return SUM('Table'[Sales])/a

[ryan_mayu]

@Nun 

you can try this

Measure = 
VAR a=CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Category]="Pre"))/CALCULATE(SUM('Table'[SalesEUR]),FILTER('Table','Table'[Category]="Pre"))
return CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Category]="After"))/a

[Nun]

@ryan_mayu 

thanks for supporting.

Using your measure, when I filter "After", the result I get is "infinity". it looks like

CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Category]="After"))/a

a is 0 when in a table I have the measure and I would like to see(I use a filter visualization) the result of "After".

Thanks!

[ryan_mayu · Accepted solution]

@Nun 

if you have a filter of category, you can try this

Measure = 
VAR a=CALCULATE(SUM('Table'[Sales]),FILTER(ALLEXCEPT('Table','Table'[Currency code]),'Table'[Category]="Pre"))/CALCULATE(SUM('Table'[SalesEUR]),FILTER(ALLEXCEPT('Table','Table'[Currency code]),'Table'[Category]="Pre"))
return SUM('Table'[Sales])/a

[Nun]

@ryan_mayu 

hello,

I used your formula, but when I check the results, are incorrect especially the total.

The formula works but the results are incorrect. In your as total you get 385 when the correct result is 250, as well the result in the measure for some currencies are not correct.

Thanks!

[ryan_mayu · Accepted solution]

@Nun 

you can create another measure

Measure 2 = SUMX('Table',[Measure])

[Nun]

Great!!!

Thanks a lot @ryan_mayu !!