topic Re: Switch between Currency and Number in DAX Commands and Tips

Switch between Currency and Number

joshua1990 — Sat, 27 May 2023 11:10:58 GMT

Hi all!

I have a report where I would like to put a switch on top as a slicer to switch between currency and units.

For this objective I started with this approach:

SWITCH A = VAR _Selection = SELECTEDVALUE('Currency/ Volume'[ID]) VAR _Volume = FORMAT(DIVIDE(SUM('Sales'[Value 1]),1000), "#,##K") VAR _Dollar = FORMAT(DIVIDE(SUM('Sales'[Value 1 $]),1000), "$ #,##K") RETURN IF(_Selection = 1, _Dollar, _Volume)

I have an unrelated table with 2 columns

Dimension	ID
Currency	1
Volumne	2

Based on the selection of ID (1 or 2), I switch within the measure to Units or Dollars.

But now I get a Text Format as a result in the matrix. I would still like to have a number column in the matrix in the end with all values on the right alligned.

Any thoughts / optimizations on the approach above?

How would you do it?

Re: Switch between Currency and Number

joshua1990 — Sun, 28 May 2023 10:24:28 GMT

No ideas?

Re: Switch between Currency and Number

ValtteriN — Sun, 28 May 2023 14:26:13 GMT

Hi ,

I would do this with a calculation group:

Expression:selectedmeasure()

Format string expression:

IF(SELECTEDVALUE('Selection for V/C'[value]="Currency", "$ #,##K", selectedmeasureformatstring())

joshua1990

Re: Switch between Currency and Number

joshua1990 — Mon, 29 May 2023 11:07:50 GMT

ValtteriN THanks a lot! Any workourant without Tabular Editor? I am not allowed to use this software.

Re: Switch between Currency and Number

ValtteriN — Mon, 29 May 2023 12:43:29 GMT

joshua1990 you can also manage calculation groups in Visual studio or after the upcoming release announced at MS Build calculation groups will be available in Power BI desktop. I think this was scheduled for June's release.

Re: Switch between Currency and Number

joshua1990 — Wed, 31 May 2023 09:48:28 GMT

ValtteriN : Thanks a lot! Could you please share the Power BI File as well?

Re: Switch between Currency and Number

ValtteriN — Thu, 01 Jun 2023 03:31:33 GMT

joshua1990

Sure,

Here is an example file

Re: Switch between Currency and Number

joshua1990 — Thu, 01 Jun 2023 15:57:37 GMT

ValtteriN : Thanks a lot for providing this. It seems like the same column is display once in units and once in dollars, right?

But what I need is something the also multiplies the units into a dollar value. I have a column with units, I have a column with the price per unit and then if needed I have a calculated column with the total dollars (units x price)

Re: Switch between Currency and Number

joshua1990 — Sat, 03 Jun 2023 05:24:59 GMT

ValtteriN :Thanks for sharing. Is it possible to use the same approach to switch between a Volume and a Dollar column with the indidivudal format? As of now it changing just the format for a single column, right?