Format Column which has multiple currencies with correct currency symbol

[Anonymous]

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/2021	50	GBP	UK
13/01/2021	70	GBP	UK

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 Date	Net 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.

[v-zhangti · Accepted solution]

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.

[Abhishekbi]

DAX has worked but the data type is text and now i can not calculate average and sum. Please help with that

[Abhishekbi]

@v-zhangti  please help

[Greg_Deckler]

@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]

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.

[VahidDM]

Hi @Anonymous 

check this link:

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

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

[bcdobbs]

They are now supported in power bi. You do however have to set them

up using tabular editor.

[VahidDM]

@Anonymous 

Do you want two calculated tables or a measure?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Just a single measure. For the visualisation, I will have 2 seperate tables which will be just filtered on country. I have updated my post to make this more clear.