Forum Discussion

Yew-Slaston's avatar
Yew-Slaston
New Member
5 years ago
Solved

RANKX as measure with variable table

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 R...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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's avatar
Yew-Slaston
New Member
5 years ago

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