Formatting multiple currency codes in single fact

[Mark]

Hi all,

Please see attached simplified pbix:
Multiple Currency Codes in Single Fact.pbix (66.4 KB)

I have a very simple fact table [Sales], containing 6 order lines from 3 different regions: UK, Australia and Europe. Each line has the local currency code stamped on it:

We need the ability to convert the Order Line value into either GBP, EUR or leave as the local currency, which will be the default. The user does this by selecting the Curreny Code from a lookup table, ‘Report Currency’:

I have created a measure [Converted Value] which does this.

When the Currency is set to ‘Local’, the [Converted Value] matches that of the original Order Value, but the measure outputs the relevant currency symbol which it retrieves from dim table [Reporting Currency Symbols], based on the Currency Code stamped on the line.

Note that the total line does not output the currency, if the table contains a mix of currencies:

And when the Currency is set to GBP or EUR, the measure converts all values to that currency, based on rates found in table [Exchange Rates] and the Total line does now output the symbol as all values are of the same currency.

I was really pleased with this result, but have found that if I add column ‘Region’ from my ‘Regions’ dim table, that the table starts to include blank rows:

I’ve also found that if I remove the FORMAT function from my [Converted Value] measure, which is what outputs the correct currency symbol then the table behaves as expected?

So I am confused as to why when adding a field from a dim table that the measure stops working as expected, but if I remove the FORMAT function it is ok.

Many thanks

Mark

[Harsh]

Hello @Mark,

Thank You for posting your query onto the Forum.

The reason why it’s showing the results as blank rows is because in the case of “FORMAT()” function it converts the numerical values into the text format.

So when there’s no result in the case of numerical values, they’re simply ignored and that line gets removed from the visual. But since in the case of “FORMAT()” function, we put our logic/evaluation results inside the quotation marks i.e., “” and therefore, when there’re no values against those lines, it converts them into blank and those blanks are represented as “” marks.

See the below provided screenshot -

In the PBIX file, I wrote one measure with format function and one without that function. So in the case, of with function, it evaluated the results as “” when there was no value found against that row.

These were the measures -

Total Sales Without FORMAT = 
SUM( Data[Amounts] )

Total Sales With FORMAT = 
FORMAT( SUM( Data[Amounts] ) , "$#,##0.00" )

So now, to ignore those blanks from the visual while using the “FORMAT()” function. That second measure can be written like this -

Total Sales With FORMAT - Ignoring Blanks = 
VAR _Total_Sales = 
SUM( Data[Amounts] )

VAR _Format_Total_Sales = 
FORMAT( _Total_Sales , "$#,##0.00" )

VAR _Results = 
IF( ISBLANK( _Total_Sales ) , 
    BLANK() , 
    _Format_Total_Sales )

RETURN
_Results

After writing the above provided measure, you’ll see that blanks will get eliminated from the visual. Below is the screenshot provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Format Function - Harsh.pbix (18.4 KB)

[Mark]

Hi @Harsh

Well that was an easy fix!

Many thanks for the quick response, report now working as expected with the inclusion of IF(ISBLANK) in my dynamic measure.

Thanks

Mark

[Harsh]

Hello @Mark,

You’re Welcome!!!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh

[Mark]

Hi @Harsh

I’ve come upon a problem of using FORMAT to apply the correct currency symbol, based on the currency code as stamped on the transaction. Neither Power Bi or Excel (if data is exported) will correctly sort the values:

I guess this is due to the fact as you say that FORMAT creates a text string.

Can you think of another way where we can apply the correct currency code but also have Power BI / Excel recognise it as a number?

Thanks!

[Harsh]

Hello @Mark,

Once we implement the “FORMAT()” function inside a measure, it converts the results into a text format. So the answer is - “NO, there’s no way Power BI can have results in a text format but showcase them as numericals.”

As far as sorting related problem is concerned. Below is the revised DAX measure alongwith the screenshot of the final results provided for the reference -

Total Sales With FORMAT - Ignoring Blanks = 
VAR _Total_Sales = 
SUM( Data[Amounts] )

VAR _Format_Total_Sales = 
REPT( UNICHAR( 8203 ) , _Total_Sales ) & FORMAT( _Total_Sales , "$#,##0.00" )

VAR _Results = 
IF( ISBLANK( _Total_Sales ) , 
    BLANK() , 
    _Format_Total_Sales )

RETURN
_Results

As you can observe in the above screenshot, now the figures are getting sorted properly. There’s a problem with this method, when the results are exported to an Excel file. Below is the screenshot attached -

When we export it to an Excel file, it showcases that there’re blank records exported but that’s not the case. When you select or click that particular cell, you’ll be able to see the numbers inside a formula bar. So this is the problem you might face when you export the results to your Excel file but as far as sorting problem inside Power BI is concerned, that’s sorted.

I’m also attaching the working of the PBIX file as well as providing a link below which I’ve referenced to tackle the sorting problem and a link from the DAX guide on a “FORMAT()” function for your reference purposes.

Thanks and Warm Regards,
Harsh

Format Function - Harsh v2.pbix (19.4 KB)

Custom Sort Order Within a DAX Measure -

FORMAT – DAX Guide