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 DateNet SaleCurrencyCountry
12/01/2021100USDUS
12/01/2021200USDUS
13/01/2021300USDUS
12/01/202150GBPUK
12/01/202150GBPUK
13/01/202170GBPUK

 

This table then gets summarized into total sales for each day by summing the "Net Sale" column.  Ultimately, there'll be seperate dashboards for each country.

 

What I want is the below table with the $ symbol when the currency is USD and the £ symbol when the currency is GBP. We can assume that each country uses just one currency for our case.

 

Table:

Order DateNet Sale
12/01/2021$300
13/01/2021$300
12/01/2021£100
13/01/2021£70

 

What I've tried but doesn't seem to work:

1. Use the Formatting option on the "Net Sales" variable. However, this only let's you choose either $ or £ and doesn't allow logic.

2. Creating a new column with DAX code such as:

I did try some other DAX code which formatted it with the correct currency symbol but then it turned the variable into a text data format and so you can't summarize the numbers. 

 

Is there anyway to do this? Another way I could do it is just duplicate the NET_SALES column and call it NET_SALES_UK and NET_SALES_US, then apply differnet formatting to each column but I thought this is kind of a waste and surely there should be another way for example if I had 10 different currencies then I would have to duplicate the column 10 times.

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.

10 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Vahid,

       

      That looks promising but it says calculation groups are not available in Power BI. Thus, currently the remaining part of this article can only be implemented in Azure Analysis Services or Analysis Services 2019

  • 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.