Format Return Value from Switch Statement as Percentage

[fibaek]

Hello Good People,

I have a matrix with a measure from several switch statements in variables. The return value is simply the variables added together – only one of them will have an actual numeric values and the rest will hold the value BLANK(). One of the statements returns a percentage value that I would like to format as “,0.0%”, however it seems like the FORMAT function cannot handle the scalar value returned by the SWITCH.

The calculation is:

VAR VAR1 = SWITCH()...
VAR CostPct = 
SWITCH(
    TRUE(),
//FORMAT( //does not work
    SELECTEDVALUE('Table'[MeasureIndex]) = 3,
            DIVIDE(
                'Table2'[Measure],    
                CALCULATE (
                        [Measure],
                        REMOVEFILTERS (
                        '_Dimension'[Dim1],
                        '_Dimension'[Dim2],
                        '_Dimensions'[Dim3]
                        )
                   )
            ),
//”percent”),
            BLANK()
    )
VAR VAR2 = SWITCH()...

I have tried to use both CONVERT and VALUE in the switch and after the RETURN to force the value to be numeric and both the returned measure and ‘Table2’[Measure] are formatted as Decimal Number. However the Matrix visual that is used continuously throws an error:

“MdxScript(Model) (1224, 7) Calculation error in measure ‘Table’[Calculated Measure]: Cannot convert value ‘-8.84%’ of type text to type Numeric/Date”

Is it possible somehow to format the return value from one of the variables as %? I can’t format the measure as % because the value is only percentage in one case.

My RETURN value is:

RETURN
IF(ISBLANK(VAR1),0,VAR1)
    + IF(ISBLANK(CostPct),0,CostPct) //This one if the percentage value
//    + IF(ISBLANK(CostPct),0,FORMAT(CostPct,”,0.0%”)) //Tried, but isn’t working
    + IF(ISBLANK(VAR2),0,VAR2)
    + IF(ISBLANK(VAR3),0,VAR3)

I am aware that I can achieve the same functionality with calculation groups, however setting DiscourageImplicitMeasures=TRUE is unfortunately not an option for this dataset.

Any help is appreciated and kudos will naturally be awarded.

/Sune

[AllisonKennedy · Accepted solution]

@fibaek  

FORMAT will convert the value to text , so you can't use the + operator as addition anymore in this scenario. 

You might find the COALESCE function helpful? 

RETURN
COALESCE( VAR1, FORMAT(CostPct,”,0.0%”),VAR2, VAR3, 0)

[AllisonKennedy · Accepted solution]

@fibaek  

FORMAT will convert the value to text , so you can't use the + operator as addition anymore in this scenario. 

You might find the COALESCE function helpful? 

RETURN
COALESCE( VAR1, FORMAT(CostPct,”,0.0%”),VAR2, VAR3, 0)

[fibaek]

Hi @AllisonKennedy 

Thank you for a quick reply. I was under the impression that VALUE() converted from string to numeric though, which is why I tried it.

Anyway, COALESCE works to some extent. However all measures after the formatted one are blank when they are selected in the slicer. The measure – VAR1 in the example – before FORMAT returns a value, but VAR2 and VAR3 do not when placed after the FORMAT(). The three other variables are identical except for the measures used in the calculation.

Apparently the order in the RETURN matters, so:

COALEASCE(VAR1,VAR3,VAR4,FORMAT(VAR2,”percent”)

works fine. Whereas

COALEASCE(VAR1,FORMAT(VAR2,”percent”),VAR3,VAR4)

does not.

Any ideas? 

On a side note: Oddly FORMAT(value,",0.0%") produces a different result in DAX as opposed to a calculation group in Tabular Editor. With DAX I get something like ,52.3% and when used in a calculation group it returns the expected format of 52,3% (European notation with , as decimal separator and . for thousands.) 

 Thank you for pointing me in the right direction - you help is very much appreciated!

/Sune

[AllisonKennedy · Accepted solution]

@fibaek  You're welcome! 

As for order of return statement, yes, the order matters - sorry I did not catch that in my original reply. Since you're formatting as text, it's no longer blank so the COALESCE won't move on to the next non-null value. You could nest an IF(ISBLANK(Var2), FORMAT(VAR2, "percent")) inside the COALESCE and then I think order won't matter.

As for getting a different result in DAX For the formatting - are you using the same format as in the calculation group? Are you using the built in percent format or FORMAT(value,",0.0%")  ??

[fibaek]

@AllisonKennedy Lovely - wrapping the format in an if works like a charm:

IF(ISBLANK(VAR2),BLANK(),FORMAT(VAR2,"percent"))

The users will have to live without the pretty colours from conditional formatting 🙂

I use the same format - ",0.0%" - in both the FORMAT() statement in DAX and the Format String Expression in Tabular Editor (Using "percent" in tabular editor literally returns the word percent in all cells.)

It's not a big deal. I'll probably ditch the decimals anyway. Just one of those curiosities the pop up now and again. 

Again: Thank you for your help!

/Sune