How to format my SWITCH Measure as Currency and integer depending on filter

[magnifybi]

Measure Selection =

SWITCH( TRUE(),

VALUES('Measure Dimensions'[Measure]) = "Total Sales", Transactions[Sales Value Total],

VALUES('Measure Dimensions'[Measure]) = "Total Quantity", Transactions[Quantity Total],

VALUES('Measure Dimensions'[Measure]) = "Total Transactions", Transactions[Transaction Count],

BLANK())

I have a measure that works off a filter to bring back sales, quantity or transaction count, what I want is the sales to be formatted as $ and the other two to be integer 

Any ideas how to solve this one?

[emilmorkeberg]

From my understanding what you want to do is having the Total Quantity and Total Transactions as whole numbers and Total Sales as $. You can do that in the Switch by using the formula below, however the output will be a string, thus you cant sort it AFAIK.

Measure Selection =
SWITCH( TRUE(),
VALUES('Measure Dimensions'[Measure]) = "Total Sales", FORMAT(Transactions[Sales Value Total],"Currency"),
VALUES('Measure Dimensions'[Measure]) = "Total Quantity", Transactions[Quantity Total],
VALUES('Measure Dimensions'[Measure]) = "Total Transactions", Transactions[Transaction Count], BLANK())

You could also just use 3 bookmarks (by creating 3 buttons) instead of the switch and by that format the Total Sales measure as $ and the two others and whole numbers.

EDIT: Just saw this thread was made in 2020... 🙂

[PowerUnilam]

I Totally understand you, I have the same issue. I wrote sth like this, but the problem is that it is a string format, so that it doesnt order the column descending or ascending properly. Have you find a better way? Thanks

Total Seleccion =

IF(ISCROSSFILTERED(Selector[Seleccion]),

SWITCH(TRUE(),

VALUES(Selector[Seleccion]) = "Ventas $", Format(Ventas[Ventas $],"Currency"),

VALUES(Selector[Seleccion]) = "Cantidad", [Cantidad SKU Vendida],

Ventas[Ventas $]),

Ventas[Ventas $])

[amitchandak]

@magnifybi , refer if this can help

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[magnifybi]

Hey, thanks for the reply, I don't see how that thread helps with my formatting of the results, have I missed something?

[V-lianl-msft]

Hi @magnifybi ,

Sorry, based on the formula you gave, I could not quite know your scenario.

Please try to format the columns involved in the formula to the data type you want.

If I mistake, you can paste some screenshots, or share the sample pbix via cloud service like onedrive for business.

Please mask any sensitive data before uploading

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[magnifybi]

Hey Liang,

Essentially the first SWITCH option is a currency ($) and the reset are just whole numbers, I can only format the whole measure as one or the other, but wanted to know if I can chnage the formula so that when I use a filter to SWITCH between the measures I want the formatting to correspond to the respective SWITCH choice.

Thanks,

J

[V-lianl-msft]

Hi @magnifybi ,

It is very difficult to analyze without looking at the data and just by imagining. See if you can paste the screenshot of the detail representing any sample data or share the sample pbix via cloud service like onedrive for business.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[magnifybi]

Example here, you can see I've selected Sales and it doesn't show a $ amount is just shows a number, I want quantity to be a whole number but sales to be a $

[magnifybi]