Trouble with RankX

[Anonymous]

Objective: Have a column presenting the rank of customer based on total sales in descending order

Data Format:

What has not worked:

Ranking =

RANKX(

ALLSELECTED(Table_1046),

CALCULATE(SUM(Table_1046[Extended Price]))

,,DESC

)

Also tried:

Rank v2 = IF(HASONEVALUE(Table_1046[Billing Customer Number]), RANKX(ALL(Table_1046[Billing Customer Number]), [Parts Sales]) )

Resulted in: 

Definitely feeling like an idiot here as this doesn't seem like this should be as challenging as it has turned out to be

[parry2k]

@Anonymous there is detail blog post on RANK, here is link of 1 of 3. hope this helps.

[Anonymous]

Thank you for the link.

Should I treat the Billing Customer Number as the subcategory then? Attempting based on that does seem to rank them, but not quite like I'd imagined.

DAX attempted:

Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]))

Result: 

It seems to be working to some extent, but I need to figure out why the numbers are jumping around like that. I tried adding the ASC or DESC to the DAX statement, but it did not change anything. Perhaps I'm putting it in the wrong place?

Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]),,ASC)

Rankv4 = RANKX(FILTER(Table_1046, Table_1046[Billing Customer Number]=EARLIER(Table_1046[Billing Customer Number])), SUM(Table_1046[Extended Price]),,DESC)

[parry2k]

@Anonymous share the sample data in excel using one drive/google drive and i will send you the solution. Remove any sensitive information.

[Anonymous]

Drive document. Thanks for your help

https://docs.google.com/spreadsheets/d/1yBowbuvQFJ0bOpWWYybjLVeRB_4B4ElTNJizBZnmc_8/edit?usp=sharing

[parry2k]

@Anonymous here is dax expression as a measure, you can change asc or desc, based on your needs.

Rank = RANKX( ALL( Sheet1[Billing Company Name]), [Ext Price], , ASC)

[Anonymous]

Unfortunately still no dice:

Rank v3 = RANKX(ALL(Table_1046[Billing Company Name]), [Parts Sales], , DESC)

Produces:

 It's all a rank of 1 except for the last few observations, which are negative values, and those are all rank 383. 

Given that this doesn't seem to be working well this way, is there a way of using the TopN filter and generating a rank column from that? Thanks again for your help 

Parts Sales is just:

Parts Sales = SUM(Table_1046[Extended Price])

[parry2k]

@Anonymous are you adding rank as a new column or measure? It should be measure.

attached file is create from your sample data.

[Anonymous]

Created as measure. It definitely works perfectly on your attachment. I'll do some more digging to see what could be interfering. Thanks again for your help. I really appreciate it