RANKX as measure with variable table

[Yew-Slaston]

Dear community. What I'm doing wrong?

I get for each customer rank 1.

The idea is to calculate dynamic rank in a table which is a variable, depending on current filter context.

DynRank =
VAR Revenue = sum(tbSales[sales])
VAR tbTemp=
filter(SUMMARIZE(tbSales;tbSales[customers];"Revenue";sum(tbSales[sales]));[Revenue]>0)
VAR result = RANKX(all(tbTemp);[Revenue];;ASC)
Return
result

[v-xulin-mstf · Accepted solution]

Hi @Yew-Slaston

You can create virtual table 'filter_table' based on the filter.

Then try measure as:

= RANKX(
       'filter_table',
       Calculate(
            sum(tbSales[sales]),
            ALL('tbSales')
       )
)

If the issue remains unresolved, please provide the pbix file after removing sensitive information.

Best Regards,
Link

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

[Jihwan_Kim]

Hi, @Yew-Slaston 

Please try something like the below.

DynRank =
RANKX (
FILTER (
SUMMARIZE (
ALL ( tbSales ),
tbSales[customers],
"Revenue", SUM ( tbSales[sales] )
),
[Revenue] > 0
),
CALCULATE ( SUM ( tbSales[sales] ) ),
,
ASC
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Yew-Slaston]

thank you, your measure works just for dimension customers. However, what I'm trying to achieve is to get the rankings for whatever row cotext I have on my worksheet. As soon as I change the context filter to customer group, I end up with 1 for every customer group. So, my thought is to make a virtual table depending of filter contect on the worksheet and then derive the rank based on current filter context. So that no matter what dimension and filter are on the sheet, I always get the correct ranking