Forum Discussion

Pretengineer's avatar
Pretengineer
Frequent Visitor
4 years ago
Solved

Measure Not Correct Currency

Have a table called Cost_Data and it has costs in three different currencies USD, GBP, and EUR.  Another table called Currency_Convert with Columns Currency, Symbol, Format.  I am trying to have a sl...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Pretengineer ,

    The reason why the line chart is not displayed after using the new measure is that the data obtained in the FORMAT format is essentially a text string. Even if the data format is changed to number or currency, it cannot be sorted by size, but only in alphabetical order. 

    But the Values in the line chart should be of number data type, so it can't display anything.

    If you want the symbol displaied correctly in the line chart, you should have three measures, set the corresponding currency symbol for each measure, here's my solution.

    Cost EUR' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "EUR",
    MAX ( 'Cost_Data'[Cost EUR] ),
    BLANK ()
)

    Cost GBP' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "GBP",
    MAX ( 'Cost_Data'[Cost GBP] ),
    BLANK ()
)

    Cost USD' =
IF (
    SELECTEDVALUE ( Currency_Convert[Currency] ) = "USD",
    MAX ( 'Cost_Data'[Cost USD] ),
    BLANK ()
)

    It get the correct result for each currency.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

tackytechtom's avatar
tackytechtom
Most Valuable Professional
4 years ago

Hi Pretengineer ,

 

Here a solution:

 

 

 

And here your updated DAX measure:

True Currency = 
Var USD_Cost = Max(Cost_Data[Cost USD])
Var GBP_Cost = Max(Cost_Data[Cost GBP])
Var EUR_Cost = Max(Cost_Data[Cost EUR])
Var Currency_ID = Cost_Data[Currency Selected]

Return
SWITCH( 
   TRUE(),
        Currency_ID = "USD" , FORMAT(USD_Cost,"$#,##0"),
        Currency_ID = "GBP" , FORMAT(GBP_Cost,"£#,##0"),
        Currency_ID = "EUR" , FORMAT(EUR_Cost,"€#,##0"),
        0)

Does this solve your issue? 🙂

 

 

[EDIT]

If you'd like to show two decimals, then you can use this code here:

True Currency = 
Var USD_Cost = Max(Cost_Data[Cost USD])
Var GBP_Cost = Max(Cost_Data[Cost GBP])
Var EUR_Cost = Max(Cost_Data[Cost EUR])
Var Currency_ID = Cost_Data[Currency Selected]

Return
SWITCH( 
   TRUE(),
        Currency_ID = "USD" , FORMAT(USD_Cost,"$#.00"),
        Currency_ID = "GBP" , FORMAT(GBP_Cost,"£#.00"),
        Currency_ID = "EUR" , FORMAT(EUR_Cost,"€#.00"),
        0)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

  • Pretengineer's avatar
    Pretengineer
    Frequent Visitor
    4 years ago

    @tomfox 

    Thanks for the suggestion, I actually tried this and it does work for the table but it doesn't work in the chart.  I still don't understand why the chart doesn't work.   Also, in the table the total for that column is wrong also.