RANKX DAX column help

[alexei7]

Hi,

I'm trying to create a column that will rank Invoices across Suppliers.

This needs to be a column so that I can then use this Rank column to create a cumulative total and then (hopefully a measure to flag/show the Suppliers which make up the top 80% of our spend.

Having googled and browsed this forum i'm struggling to come up with something that works for me.

My data looks like this:

SupplierCode	InvoiceNumber	Invoice Amount
S1000000	:892347892	£100
S1000001	Jeff:2343234	£101
S1000002	Jeff:239048912	£10
S1000002	Susan:93240923	£5
S1000002	:9239239	£50
S1000003	:2930499023	£25
S1000001	Greg:8293489	£5

I want to create either of the two results:

1. A table with the extra rank column, where rank is appled based on the total across the SupplierCode

SupplierCode	InvoiceNumber	Invoice Amount	Rank
S1000000	:892347892	£100	2
S1000001	Jeff:2343234	£101	1
S1000002	Jeff:239048912	£10	3
S1000002	Susan:93240923	£5	3
S1000002	:9239239	£50	3
S1000003	:2930499023	£25	4
S1000001	Greg:8293489	£5	1

2. If the above is not possible, a derived table such as the below:

SupplierCode	Invoice Amount	Rank
S1000000	£100	2
S1000001	£101	1
S1000002	£10	3
S1000003	£5	4

Much appreciated,

Alex

[v-frfei-msft]

Hi @alexei7,

To create two measures as below.

total = CALCULATE(SUM(Table1[Invoice Amount]),ALLEXCEPT(Table1,Table1[SupplierCode]))

rankx = RANKX(ALLSELECTED(Table1[SupplierCode]),[total],,DESC,Skip)

Also to create a calculated columm worked well.

rankxcc = RANKX(ALLSELECTED(Table1[SupplierCode]),[total],,DESC,Skip)

For more details, please check the pbix as attached.

Regards,

Frank

[Zubair_Muhammad · Accepted solution]

@alexei7

As a calc column,,,try

Calc Column =
RANKX (
    Table1,
    CALCULATE (
        SUM ( [Invoice Amount] ),
        ALLEXCEPT ( Table1, Table1[SupplierCode] )
    ),
    ,
    DESC,
    DENSE
)

[alexei7]

Thanks for your help @Zubair_Muhammad

Unfortunately this isn't working for me.

I probably should have mentioned that my dataset includes about 30 other columns (which are not required for this calculation). I guess that might be important when using ALLEXCEPT.

See below for dummy data with the formula you helped with:

Supplier Code	Invoice Amount	* Rank Column
S687255	£             23,029,923	4
S241863	£               1,100,000	52
S712616	£               2,700,210	75
S331533	£               3,700,210	126
S943796	£                   800,000	126
S14822	£                   200,000	132
S229347	£                   400,000	163
S714313	£               6,333,333	168
S18322	£                   100,000	214
S69059	£                     52,000	215
S628244	£                   120,000	230

Any further ideas?

Thanks again

Alex

[Zubair_Muhammad]

@alexei7

Any number of columns shouldn't make a difference I believe.

Could you share your file?

Is the result below from a Table Visual in Report....Perhaps it is summing the RANKs...may be you need to mark the RANK column as "Dont summarize"

Could you share screen shot from dataview

[alexei7]

Thanks so much @Zubair_Muhammad - the issue was summing the RANKs, also I think the reason why my own attempts were failing.