Variance column not sorting correctly

DAX DAX Calculations
1

Hi All

I have a variance column, with the following formula:
v LY =
VAR ExecVar = [Exec Var Table Data] = “Var.”
VAR ExecVarPerc = [Exec Var Table Data] = “% Var.”
VAR TOREPERC = [Table Data] = “% TORE”
VAR PORSum = [Table Data] = “POR”
VAR PARSum = [Table Data] = “PAR”
VAR ACTUALS = [Table Data] = “Actuals”
VAR PeriodTableYTD = [Period Table Data] = “YTD”
VAR PeriodTableMTD = [Period Table Data] = “MTD”
VAR TOREPERCMTD = FORMAT( [% TORE MTD.] - [% TORE LY MTD.] , “0.0%” )
VAR TOREPERCYTD = FORMAT( [% TORE YTD.] - [% TORE LY YTD.] , “0.0%”)
VAR Remainder = [CY.] - [LY.]
VAR RemainderPerc = FORMAT(DIVIDE([CY.] - [LY.] , [LY.]) , “0.0%”)
RETURN
SWITCH (
TRUE (),
ExecVar && TOREPERC && PeriodTableMTD, TOREPERCMTD,
ExecVar && TOREPERC && PeriodTableYTD, TOREPERCYTD,
ExecVarPerc && TOREPERC && PeriodTableMTD, TOREPERCMTD,
ExecVarPerc && TOREPERC && PeriodTableYTD, TOREPERCYTD,
ExecVar, Remainder ,
ExecVarPerc , RemainderPerc )

And thew above column is set to sort from highest to lowest.

When I select the “value variance” (“Var.”) it sorts correctly, however, when selecting the “% variance” ("% Var."), it does not sort the column correctly.

Is there an error in my formula?

2

Hello @Stuart,

Thank You for posting your query onto the Forum.

Is your “% Variance” ("% Var.") wrapped by the function “FORMAT()”? in your primary formula logic?

If yes, then the “% Variance” ("% Var.") has been converted into the Text. And Text cannot be sorted with Highest to Lowest logic.

Thanks & Warm Regards,
Harsh

3

Hi @Harsh, no, it is only formatted as %age in the above dax - VAR RemainderPerc = FORMAT(DIVIDE([CY.] - [LY.] , [LY.]) , “0.0%”)

4

Hello @Stuart,

Since your you’ve changed your Number format to the Text format by wrapping it under the “FORMAT()” function it is no longer under the number format because although on the screen it’s seen as % it’s actually converted into the text because the Format of % is mentioned under quotes and anything mentioned under the quotes is regarded as text format.

And please note also you cannot mention it as shown below by removing the quotes -

VAR RemainderPerc = FORMAT(DIVIDE([CY.] - [LY.] , [LY.]) , 0.0%)

It will simply show the error because any symbol is being considered as text so there’s no other alternative.

Sam has also said in one of the video of Learning Summit Series of Financial Reporting that once you apply your formula with the FORMAT() logic it is converted into the text. He mentioned this when one of the member asked the question on the live video chat.

Thanks & Warm Regards,
Harsh

5

Thanks. Is there anyway to get around this?

6

Hello @Stuart,

What you can do is create separate measure for the % figures and the normal or actual figures. And for the % figures then you can apply the % format from “Measure Tools” section by selecting the % symbol.

Also I’m providing 2 links where similar sort of query was asked onto the Forum earlier regarding the % format.

Hoping you find this useful.

Thanks & Warm Regards,
Harsh

7

Hi @Stuart , did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!