Sorting by another column measure doesnt work correctly

[lucas_canova]

Hi, i'm new in pbi, and im trying to perform delta calculations between my columns. I did it and finished my measure, like bellow:

DIFF = 
VAR _PE = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "PE")
VAR _RF = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "RF")
VAR _REAL = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "REAL")
VAR _0 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 0 + 12")
VAR _1 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 1 + 11")
VAR _2 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 2 + 10")
VAR _3 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 3 + 9")
VAR _4 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 4 + 8")
VAR _5 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 5 + 7")
VAR _6 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 6 + 6")
VAR _7 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 7 + 5")
VAR _8 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 8 + 4")
VAR _9 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 9 + 3")
VAR _10 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 10 + 2")
VAR _11 = CALCULATE(SUM(BASE_TOTAL[VALOR]), BASE_TOTAL[VERSAO] == "BN 11 + 1")

VAR _SLICER = CONCATENATEX(ALLSELECTED(BASE_TOTAL),BASE_TOTAL[VERSAO],"|")

RETURN

SWITCH(TRUE(),
AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) =  "△ PE X RF",AND(PATHCONTAINS(_SLICER,"PE"),PATHCONTAINS(_SLICER,"RF"))), ABS(_PE-_RF),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ REAL X PE",AND(PATHCONTAINS(_SLICER,"PE"),PATHCONTAINS(_SLICER,"REAL"))), ABS(_REAL-_PE),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ REAL X RF",AND(PATHCONTAINS(_SLICER,"RF"),PATHCONTAINS(_SLICER,"REAL"))), ABS(_REAL-_RF),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ REAL X BN 2 + 10",AND(PATHCONTAINS(_SLICER,"BN 2 + 10"),PATHCONTAINS(_SLICER,"REAL"))), ABS(_REAL-_2),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ REAL X BN 4 + 8",AND(PATHCONTAINS(_SLICER,"BN 4 + 8"),PATHCONTAINS(_SLICER,"REAL"))), ABS(_REAL-_4),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ REAL X BN 5 + 7",AND(PATHCONTAINS(_SLICER,"BN 5 + 7"),PATHCONTAINS(_SLICER,"REAL"))), ABS(_REAL-_5),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ PE X BN 2 + 10",AND(PATHCONTAINS(_SLICER,"PE"),PATHCONTAINS(_SLICER,"BN 2 + 10"))), ABS(_PE-_2),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ PE X BN 4 + 8",AND(PATHCONTAINS(_SLICER,"PE"),PATHCONTAINS(_SLICER,"BN 4 + 8"))), ABS(_PE-_4),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ PE X BN 5 + 7",AND(PATHCONTAINS(_SLICER,"PE"),PATHCONTAINS(_SLICER,"BN 5 + 7"))), ABS(_PE-_5),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ RF X BN 2 + 10",AND(PATHCONTAINS(_SLICER,"RF"),PATHCONTAINS(_SLICER,"BN 2 + 10"))), ABS(_RF-_2),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ RF X BN 4 + 8",AND(PATHCONTAINS(_SLICER,"RF"),PATHCONTAINS(_SLICER,"BN 4 + 8"))), ABS(_RF-_4),

AND(SELECTEDVALUE(BASE_TOTAL[VERSAO]) = "△ RF X BN 5 + 7",AND(PATHCONTAINS(_SLICER,"RF"),PATHCONTAINS(_SLICER,"BN 5 + 7"))), ABS(_RF-_5),

SUM(BASE_TOTAL[VALOR]))

Note: First i created my deltas labels in rows ("VERSAO column), with my value column with no values in that, just to "DIFF" measure bring values.

The real problem begins now. I tried to sort column to show PE, RF, BN, REAL and deltas in this order. I created a "RANK" column in power query numbering PE to 1 RF to 2 etc.

My deltas go away. What i do?

[Anonymous]

Hi @lucas_canova ，

It seems illogical that you mention that there are no values in VERSAO, but then sort them. My guess is that you are trying to sort based on PE,RF. For PE, it should be 34.86, 33.44, 31.97. so you may need to add a new calculated column. Then use this calculated column to sort the diff.

rank = RANKX('Table',[diff],,DESC,Skip)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[lucas_canova]

Hi @Anonymous ,

I have that scenario, on delta columns in the begin of my matrix, i need them in the end, so i have this column order:

And i need this:

When i said no values in column value, i referred to my db. Like this:

My diff measure assign values to them          ⭡