Forum Discussion
Trouble with RankX
Objective: Have a column presenting the rank of customer based on total sales in descending order Data Format: What has not worked: Ranking = ...
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
Super User
Super UserAnonymous share the sample data in excel using one drive/google drive and i will send you the solution. Remove any sensitive information.
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)
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 helpParts Sales is just:Parts Sales = SUM(Table_1046[Extended Price])