Multiply based on currency check

[Arnaud31450]

Hi everyone,

I have values in the table "Price List item" stored as local currencies "Range1_c" e.g. 1,000

In this table I have "CurrencyIsoCode" e.g. EUR

In another Table "Table" I have values "Fxb Rate 2022" e.g. 0.22

In this "Table" I have "Currency" e.g. EUR which is linked to "CurrencyIsoCode" in my other table "ProductPricing"

I would like to create a new measure which is "Range1_USD" that takes "Range1_c" check its "CurrencyIsoCode" and multiply by "Fxb Rate 2022" corresponding value based on "Currency"

Thanks for the help.

[smpa01 · Accepted solution]

@Arnaud31450  can you try this measure

Measure =
CALCULATE (
    SUMX (
        'Price List item',
        VAR _0 = 'Price List item'[Range1_c]
        VAR _1 =
            CALCULATE (
                MAX ( 'Table'[Fxb Rate 2022] ),
                TREATAS ( VALUES ( 'Price List item'[CurrencyIsoCode] ), 'Table'[Currency] )
            )
        RETURN
            _0 * _1
    )
)

[Arnaud31450]

Thanks a lot everyone for the Help !

[v-yalanwu-msft]

Hi， @Arnaud31450 ；

You could create a measure as follows:

Range1_USD = SUM('Table'[Fxb Rate 2022])*CALCULATE( SUM('Price List item'[Range1_c]),FILTER('Price List item',[CurrencyIsoCode]=MAX('Table'[Currency])))

The final output is shown below:

If this formula does not apply to your data, can you share the relationship of your data or more details?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arnaud31450]

Superb !

But I think I have problems with my Range1_c

Which would correlate also with the fact that I can't make this one work (it still give old value and do not get me a value with the selected multiplier

[smpa01 · Accepted solution]

@Arnaud31450  looks like you missed a comma after SUMX ('Price List item'

Measure =
CALCULATE (
SUMX (
'Price List item',
VAR _0 = 'Price List item'[Range1_c]
VAR _1 =
CALCULATE (
MAX ( 'Table'[Fxb Rate 2022] ),
TREATAS ( VALUES ( 'Price List item'[CurrencyIsoCode] ), 'Table'[Currency] )
)
RETURN
_0 * _1
)
)

[smpa01 · Accepted solution]

@Arnaud31450  can you try this measure

Measure =
CALCULATE (
    SUMX (
        'Price List item',
        VAR _0 = 'Price List item'[Range1_c]
        VAR _1 =
            CALCULATE (
                MAX ( 'Table'[Fxb Rate 2022] ),
                TREATAS ( VALUES ( 'Price List item'[CurrencyIsoCode] ), 'Table'[Currency] )
            )
        RETURN
            _0 * _1
    )
)