Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Format Column which has multiple currencies with correct currency symbol

Hi,   I've got a table as below: Order Date Net Sale Currency Country 12/01/2021 100 USD US 12/01/2021 200 USD US 13/01/2021 300 USD US 12/01/2021 50 GBP UK 12/01/...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    Maybe you can try the following methods.

    Measure:

    Measure = 
CONCATENATE (
    IF (
        SELECTEDVALUE ( 'Table'[Currency] ) = "GBP", "£",
        IF ( SELECTEDVALUE ( 'Table'[Currency] ) = "USD", "$" )
    ),
    CALCULATE (
        SUM ( 'Table'[Net Sale] ),
        FILTER (
            ALL ( 'Table' ),
            [Order Date] = MAX ( 'Table'[Order Date] )
                && [Country] = MAX ( 'Table'[Country] )
        )
    )
)

    If a new currency appears, you can then write it in the IF function.

     

    Best Regards,

    Community Support Team _Charlotte

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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous I'm thinking two columns like this:

US Currency Column = IF([Country] = "US",[Net Sale],BLANK())

UK Currency Column = IF([Country] = "UK",[Net Sale],BLANK())

Format each column with the correct currency and use in your visuals.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Greg, I wanted to avoid creating 2 columns for each country for example if we had 10 currencies, I would have to make 10 new columns but if that's the only way then so be it.