Currency Format

[Molin]

Dear Community, 

I am working on a report where the users should be able to switch between 3 different currencies. 

I have created a simple table (CurrencyMeasure) and the following DAX command to support a currency slicer on the report page: 

 NET SALES =  

    IF(

        HASONEVALUE(CurrencyMeasure[Currency]),

            SWITCH(VALUES(CurrencyMeasure[Currency])

            ,"DKK",FORMAT([Net Sales (DKK)], "#,## kr.")

            ,"£",FORMAT(DIVIDE([Net Sales (DKK)]8.5),"£ #,##")

            ,"€",(FORMAT(DIVIDE([Net Sales (DKK)],"€ #,##")

            ,[Net Sales (DKK)]))

It all works fine, however the sorting of Net Sales gets rather odd in a table as its now a string (see picture).

As I would prefer to have the currency symbols in the table, is there a way to sort by highest value and not first numer in a table?

Thanks in advance.

[Molin]

Hi @johnt75, 

Thanks great feedback. I have looked into using Tabular Editor with Calculating groups. However, it tends to format all measures (some which are not currencies) when I use SELECTEDMEASURE(). Do you know a way around this? 

Thanks in advance.

[johnt75]

The format expression itself can be DAX, so you could put a SWITCH( SELECTEDMEASURENAME() in there and return different format strings dependent on which measure was currently in context

[Molin]

Hi @johnt75 , 

When I try to use either SWITCH or IF I am able to format the selectedmeasures, however I can find a way to keep the normal format for all other measure. I have a table where I want the Calculated group to format some columns with Sales and Budget but the rest of the measures should keep their normal format. 

How do I do that? 

Thanks in advance. 

[johnt75 · Accepted solution]

I think you should be able to use SELECTEDMEASUREFORMATSTRING() as the default in a SWITCH statement, so unless you explicitly specify a new format string for a measure it will use its original setting

[Molin]

Hi, 

Thanks a million. I got it solved with the SELECTEDMEASUREFORMATSTRING()

[johnt75]

You could use a field parameter. Create separate measures to return the value in the appropriate currency, each of which you can then format properly. Add all the measures to a field parameter and the user can choose between them.

[Molin]

Hi @johnt75, 

Thanks for the answer. The user should be able to change more then just the currency of Net Sales, also Buget and Forecast. Would that be possible using field parameter? I havent really played around with that feature yet. 

Thanks in advance. 

[johnt75]

If there are multiple different types of measures to change then a fields parameter is probably not the best option, you might be better off with a calculation group as that would work with any measure you passed in to it, simply perform the currency conversion on the results of SELECTEDMEASURE(). It is also possible to have different format strings for different calculation items