RANKX - help

[Andshepch]

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# 01017477	Account 91	Product 17	49.5
OP# 00876652	Account 109	Product 14	34.9
OP# 00856016	Account 64	Product 22	28.5
OP# 01007363	Account 31	Product 2	27.5
OP# 00835107	Account 61	Product 17	25
OP# 00744091	Account 98	Product 17	21.9
OP# 01010686	Account 23	Product 3	20
OP# 01017477	Account 91	Product 28	19.8
OP# 00986908	Account 2	Product 37	18.2
OP# 01017477	Account 91	Product 7	16.5
OP# 01017477	Account 91	Product 32	16.5
OP# 01017477	Account 91	Product 37	16.5
OP# 01007862	Account 69	Product 28	15
OP# 01010657	Account 104	Product 3	15
OP# 00865213	Account 33	Product 23	14.1
OP# 00744091	Account 98	Product 30	12.5
OP# 00744091	Account 98	Product 37	11.7
OP# 00876652	Account 109	Product 8	11.2
OP# 01017015	Account 19	Product 30	11
OP# 00844892	Account 99	Product 28	10.8
OP# 00986209	Account 88	Product 32	10.4
OP# 00856016	Account 64	Product 4	10.4
OP# 00876652	Account 109	Product 22	10.1
OP# 00891323	Account 3	Product 17	10
OP# 00895297	Account 6	Product 37	10
OP# 01013662	Account 7	Product 14	10
OP# 01017477	Account 91	Product 22	9.9
OP# 01017477	Account 91	Product 30	9.9
OP# 00876652	Account 109	Product 17	9.8
OP# 00978496	Account 48	Product 10	9.3

[v-juanli-msft]

Hi @Andshepch 

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.

[Andshepch]

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# 01017477	Account 91	Product 17	49.5	138.6	1
OP# 00876652	Account 109	Product 14	34.9	66	8
OP# 00856016	Account 64	Product 22	28.5	38.9	15
OP# 01007363	Account 31	Product 2	27.5	27.5	17
OP# 00835107	Account 61	Product 17	25	25	18
OP# 00744091	Account 98	Product 17	21.9	46.1	12
OP# 01010686	Account 23	Product 3	20	20	19
OP# 01017477	Account 91	Product 28	19.8	138.6	1
OP# 00986908	Account 2	Product 37	18.2	18.2	20
OP# 01017477	Account 91	Product 7	16.5	138.6	1
OP# 01017477	Account 91	Product 32	16.5	138.6	1
OP# 01017477	Account 91	Product 37	16.5	138.6	1
OP# 01007862	Account 69	Product 28	15	15	21
OP# 01010657	Account 104	Product 3	15	15	21
OP# 00865213	Account 33	Product 23	14.1	14.1	23
OP# 00744091	Account 98	Product 30	12.5	46.1	12
OP# 00744091	Account 98	Product 37	11.7	46.1	12
OP# 00876652	Account 109	Product 8	11.2	66	8
OP# 01017015	Account 19	Product 30	11	11	24
OP# 00844892	Account 99	Product 28	10.8	10.8	25
OP# 00986209	Account 88	Product 32	10.4	10.4	26
OP# 00856016	Account 64	Product 4	10.4	38.9	15
OP# 00876652	Account 109	Product 22	10.1	66	8
OP# 00891323	Account 3	Product 17	10	10	27
OP# 00895297	Account 6	Product 37	10	10	27
OP# 01013662	Account 7	Product 14	10	10	27
OP# 01017477	Account 91	Product 22	9.9	138.6	1
OP# 01017477	Account 91	Product 30	9.9	138.6	1
OP# 00876652	Account 109	Product 17	9.8	66	8
OP# 00978496	Account 48	Product 10	9.3	9.3	30

[AlB]

Hi @Andshepch 

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 )

[Andshepch]

Hi AIB,

Thanks for your reply.

THe reason that I am trying to do this is that I need to get a Top 10 list of deals for a Table Visualisation - but then I need a card underneath with the value of the rest, so what I was trying to do is use where RANK<=10 for my top ten and calculate where RANK>10 for the "rest" number - the RANK.EQ only allows me to sort e.g. in my latest dataset the tenth deal has a ranking of 119 using your formula.

Do you have any more ideas? Thank you.

Regards

Andrew 

[v-juanli-msft]

Hi @Andshepch 

Is this problem sloved?

If not, please let me know.

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.

[v-juanli-msft · Accepted solution]

Hi @Andshepch 

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.