DAX Formatting inside a Measure?

[Stoppelaar]

Hi,

I have some Measures, sales, Prices, Share in different measures, to make some of my reports more flexible would I like to have all my measures in one slicer.

So what I have done is to create a table "FilterMeasure" with the measure and then I use below DAX that connects me to the Measure.

Sales =

IF(

HASONEVALUE(Products[SHORT]),

SWITCH(

TRUE(),

VALUES(FilterMeasures[Measure]) = "Value Sales'000", [Value Sales'000],

VALUES(FilterMeasures[Measure]) = "Volume Sales'000", [Volume Sales'000],

VALUES(FilterMeasures[Measure]) = "Units Sales'000", [Units Sales'000],

VALUES(FilterMeasures[Measure]) = "Price/EAN", [Price/EAN],

VALUES(FilterMeasures[Measure]) = "Price/KG", [Price/KG],

VALUES(FilterMeasures[Measure]) = "Value%Share", [Values MS],

VALUES(FilterMeasures[Measure]) = "Volume%Share", [Volume MS],

VALUES(FilterMeasures[Measure]) = "Unit%Share", [Unit MS],

BLANK()

),

CALCULATE(

SWITCH(

TRUE(),

VALUES(FilterMeasures[Measure]) = "Value Sales'000", [Value Sales'000],

VALUES(FilterMeasures[Measure]) = "Volume Sales'000", [Volume Sales'000],

VALUES(FilterMeasures[Measure]) = "Units Sales'000", [Units Sales'000],

VALUES(FilterMeasures[Measure]) = "Price/EAN", [Price/EAN],

VALUES(FilterMeasures[Measure]) = "Price/KG", [Price/KG],

VALUES(FilterMeasures[Measure]) = "Value%Share", [Values MS],

VALUES(FilterMeasures[Measure]) = "Volume%Share", [Volume MS],

VALUES(FilterMeasures[Measure]) = "Unit%Share", [Unit MS],

BLANK()

)

,ALL(Products[SHORT]))

)

My issue now is that they all are formated in the same way, if I use "General" formatting on then they all have 2 decimals and I would like to to have wholenumber for Sales, 2 decimals for Price and Share needs to be in % with 1 decimals...

Is this possible? I have google a lot and look in this forum but I cant really find a way to use DAX to format the different measures, is there anyone who know if or how it can be done? 

Many thanks for you help!

Andreas

[PaulDBrown · Accepted solution]

Format the measure as a whole number, but in the price and % values follow this pattern:

VALUES(FilterMeasures[Measure]) = "Price/EAN", FORMAT[Price/EAN], "#,##0.00"),

and

VALUES(FilterMeasures[Measure]) = "Value%Share", FORMAT([Values MS], "#0.0%")

[Stoppelaar]

Many thanks @PaulDBrown! This give me the format I want on all the Measure!

Now next challange is sorting, because it works perfect on Val/Vol/Uni but when sorting on Price and Share it comes in nummeric order from 1to9, I have tested a bit but cant really find a solution that the highest/lowest comes first, do you know anything about this?   

[PaulDBrown]

The reason that the sorting is as you see it is because FORMAT returns a text string. You might try adding the numerical value as a new measure to sort the visual by. You can then hide it by turning off the text wrap options in the formatting pane and "hiding" the column by dragging the right boundary left to conceal it.

[mwegener]

Hi @Stoppelaar ,

you can achieve something like this with Calculation Groups.

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/