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 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

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.

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    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.