RANKX ADDCOLUMNS Variable table syntax issue

[Anonymous]

Hi All, I need help wiht a rankx within a variable table. I need to use variables instead of calculated columns as the measure I am using is dynamic and I don't want to use multiple calculated columns and switch functions. 

This is the first part of the solution, I will then add percentile rank and band results. 

TestTable:

Dimension	Value
A	1
B	2
C	3
D	4

Dax code attempt:

Rank = 
Var Tbl =       SUMMARIZE('TestTable',
                'TestTable'[Dimension],
                "Measure",SUM(TestTable[Value]))
Var AddRank =   ADDCOLUMNS(Tbl,"Rank",RANKX(Tbl,[Measure]))
RETURN
SUMX(AddRank,[Rank])

The dax is currently returning a 1 for each dimension. Is there a way to use Rankx from a variable that is not a calculated column? I'm thought I might have seen a post perhaps related to this before following a really long search I cannot find a solution that works.

[FrankAT · Accepted solution]

Hi @Anonymous 

one solution I found is the following formula (see figure 1), but it is equal with the shorter formula in figure 2.

Figure 1

 Figure 2

Some notes about your formula:

1. You have used a varibale to create a virtual table, here: Var Tbl. In RANKX() you need the ALL() function that expects only a table reference, not a table expression like Tbl.
2. The measure Sum of TestTable[Value] should be calculated outside the formula.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Thakns so much @FrankAT, exactly what I need greatly appreciated.