Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RANKX - help

Hi all,

 

I just don't seem to get how RANKX works - I have watched many videos, googled various terms and read many posts, but I still can't get RANKX to work for my data. I have a Pipeline table with Account Name, Product, Unique opportunity ID and Deal Value. There can be multiple lines for each Opportunity ID. What I need to do is Rank by Opportunity ID in the Pipeline, but I can't get it to work

 

I've tried multiple ways but none of them work - this is the closest that I have got but it is still wrong (at least for what I want to do)

 

Rank of Pipeline = rankx(ALLSELECTED('2 Pipeline'),'2 Pipeline'[Sum Pipe CV],,DESC,Dense)

This seems to rank each row within the table, but I need to rank the Opportunity ID - here is a sample of my data - any/all help is greatly appreciated - thank you.

 

Opportunity IDAccount NameProductPipe CV €M

OP# 01017477Account 91Product 1749.5
OP# 00876652Account 109Product 1434.9
OP# 00856016Account 64Product 2228.5
OP# 01007363Account 31Product 227.5
OP# 00835107Account 61Product 1725
OP# 00744091Account 98Product 1721.9
OP# 01010686Account 23Product 320
OP# 01017477Account 91Product 2819.8
OP# 00986908Account 2Product 3718.2
OP# 01017477Account 91Product 716.5
OP# 01017477Account 91Product 3216.5
OP# 01017477Account 91Product 3716.5
OP# 01007862Account 69Product 2815
OP# 01010657Account 104Product 315
OP# 00865213Account 33Product 2314.1
OP# 00744091Account 98Product 3012.5
OP# 00744091Account 98Product 3711.7
OP# 00876652Account 109Product 811.2
OP# 01017015Account 19Product 3011
OP# 00844892Account 99Product 2810.8
OP# 00986209Account 88Product 3210.4
OP# 00856016Account 64Product 410.4
OP# 00876652Account 109Product 2210.1
OP# 00891323Account 3Product 1710
OP# 00895297Account 6Product 3710
OP# 01013662Account 7Product 1410
OP# 01017477Account 91Product 229.9
OP# 01017477Account 91Product 309.9
OP# 00876652Account 109Product 179.8
OP# 00978496Account 48Product 109.3
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create measures

    Measure = CALCULATE(SUM('My test'[Pipe CV €M]),ALLEXCEPT('My test','My test'[Opportunity ID]))

Measure 2 = RANKX(ALL('My test'),'My test'[Measure],,DESC,Dense)

Measure 3 = RANKX(ALL('My test'),'My test'[Measure],,DESC,Skip)

    In this way, i can get rank1 and rank2, it seems you want rank2,

    To get top10 on the table, add [meausre3] in the visual level filter

    To get total value of "Pipe CV €M" which rank>top10, create a measure

    Measure 4 = CALCULATE(SUM('My test'[Pipe CV €M]),FILTER(ALL('My test'),[Measure 3]>10))

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Do you want to rank "Pipe CV €M" value by Opportunity ID as below?

    Measure = SUM(Sheet7[Pipe CV €M])

Measure 2 = RANKX(ALLEXCEPT(Sheet7,Sheet7[Opportunity ID]),[Measure],,DESC,Dense)

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Maggie,

       

      Thanks for the Reply.

       

      No that is not what I am trying to do - what I need is to rank the sum of CV for each Opportunity ID - the results I am trying to achieve are as below (from Excel)

       

      Opportunity idAccount NameProductPipe CV €MGroupby Opportunity IDRank of Groupby

      OP# 01017477Account 91Product 1749.5138.61
      OP# 00876652Account 109Product 1434.9668
      OP# 00856016Account 64Product 2228.538.915
      OP# 01007363Account 31Product 227.527.517
      OP# 00835107Account 61Product 17252518
      OP# 00744091Account 98Product 1721.946.112
      OP# 01010686Account 23Product 3202019
      OP# 01017477Account 91Product 2819.8138.61
      OP# 00986908Account 2Product 3718.218.220
      OP# 01017477Account 91Product 716.5138.61
      OP# 01017477Account 91Product 3216.5138.61
      OP# 01017477Account 91Product 3716.5138.61
      OP# 01007862Account 69Product 28151521
      OP# 01010657Account 104Product 3151521
      OP# 00865213Account 33Product 2314.114.123
      OP# 00744091Account 98Product 3012.546.112
      OP# 00744091Account 98Product 3711.746.112
      OP# 00876652Account 109Product 811.2668
      OP# 01017015Account 19Product 30111124
      OP# 00844892Account 99Product 2810.810.825
      OP# 00986209Account 88Product 3210.410.426
      OP# 00856016Account 64Product 410.438.915
      OP# 00876652Account 109Product 2210.1668
      OP# 00891323Account 3Product 17101027
      OP# 00895297Account 6Product 37101027
      OP# 01013662Account 7Product 14101027
      OP# 01017477Account 91Product 229.9138.61
      OP# 01017477Account 91Product 309.9138.61
      OP# 00876652Account 109Product 179.8668
      OP# 00978496Account 48Product 109.39.330
      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Hi Anonymous 

         

        You can create a calculated column with the sum of CV per opportunity ID (i.e. the column before last in the last table you show):

         

        CVSum =
CALCULATE ( SUM ( Table1[CV] ), ALLEXCEPT ( Table1, Table1[OpportunityID] ) )

        and then create another new column with the ranking:

         

        RankCol =
RANK.EQ ( Table1[CVSum], Table1[CVSum], DESC )