Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rank within a sub category

Hello,

 

I am trying to rank a seies of values. Here is a sample:

 

VendorID     Total     Classification

1                   5           N

2                   2           N

3                   7           P

4                   3           P

5                  8            E

 

I would like to rank the vendors within the classification. Here is what I tried, but I get an error:

PFP Ranking = RANKX(
FILTER(
'PFP Ranking'[Classification]
),
'PFP Ranking'[Total])
)
 
Any Ideas?
 
Cheers,
 
Peter
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column or a measure as below.

    Calculated column:

     

    Rank Column = 
RANKX(
    FILTER(
       ALL('Table'),
       [Classification]=EARLIER('Table'[Classification])
    ),
    [Total]
)

     

    Measure:

     

    Rank Measure = 
RANKX(
    FILTER(
        ALL('Table'),
        [Classification]=MAX('Table'[Classification])
    ),
    CALCULATE(SUM('Table'[Total]))
)

     

     

    Result:

     

    Best Regards

    Allan

     

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

2 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column or a measure as below.

    Calculated column:

     

    Rank Column = 
RANKX(
    FILTER(
       ALL('Table'),
       [Classification]=EARLIER('Table'[Classification])
    ),
    [Total]
)

     

    Measure:

     

    Rank Measure = 
RANKX(
    FILTER(
        ALL('Table'),
        [Classification]=MAX('Table'[Classification])
    ),
    CALCULATE(SUM('Table'[Total]))
)

     

     

    Result:

     

    Best Regards

    Allan

     

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

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    Create a new calculated measure with below DAX:

    Rank_Measure =
    RANKX(
    FILTER(
    ALL('Table'[VendorID],'Table'[Classification]),
    'Table'[Classification]=MAX('Table'[Classification])
    ),
    CALCULATE(SUM('Table'[Total]))
    )

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!