Forum Discussion

Arnaud31450's avatar
Arnaud31450
Frequent Visitor
4 years ago
Solved

Multiply based on currency check

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's avatar
    smpa01
    4 years ago

    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
    )
)
  • smpa01's avatar
    smpa01
    4 years ago

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

5 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
    Arnaud31450
    Frequent Visitor
    4 years ago

    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's avatar
      smpa01
      Community Champion
      4 years ago

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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.