topic Re: Currency Format in DAX Commands and Tips

Currency Format

Molin — Tue, 02 Aug 2022 13:35:05 GMT

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.

Re: Currency Format

johnt75 — Tue, 02 Aug 2022 16:24:27 GMT

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.

Re: Currency Format

Molin — Tue, 02 Aug 2022 19:55:24 GMT

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.

Re: Currency Format

johnt75 — Wed, 03 Aug 2022 08:12:03 GMT

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

Re: Currency Format

Molin — Thu, 04 Aug 2022 10:02:09 GMT

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.

Re: Currency Format

johnt75 — Thu, 04 Aug 2022 10:42:39 GMT

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

Re: Currency Format

Molin — Thu, 04 Aug 2022 20:43:09 GMT

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.

Re: Currency Format

johnt75 — Fri, 05 Aug 2022 08:14:36 GMT

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

Re: Currency Format

Molin — Mon, 08 Aug 2022 06:02:33 GMT

Hi,

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