RANKX across groups - dense rank not working as expected

[BeeBeeJay]

I have a table of sales quantity by product, and when I create a ranking measure showing most sold to least sold it works fine.  When I then group my products into categories and show the rank by category, the ranking is no longer dense ...... ?

Sample Table:

tblSales = DATATABLE("Row ID", INTEGER, "Product", STRING, "Qty Sold", INTEGER, { {1, "Coffee", 100 }, {2, "Tea", 120 }, {3, "Fruit Juice", 75 }, {4, "Sandwich", 50 }, {5, "Pizza", 20} })

Add measure:

Sales Rank = RANKX(ALL(tblSales), CALCULATE(SUM(tblSales[Qty Sold]) ),,DESC,Dense)

If tyou display this information in a table visual it all works as expected with tea ranked first for sales and pizza ranked last.

Next, add a Category Group into the table grouping "Sandwich" and "Pizza" into "Food", and "Tea", "Coffee", and "Fruit Juice" into "Drinks".

Add the "Category" field to the table, everything is still fine.

Remove "Product" field from the table, and the table collapses to the two Category rows - food and drink - with the correct sum of Qty Sold, but the ranking instead of being 1 and 2 is 1 and 4 - and I have no idea why.  

Any help much appreciated.

[BeeBeeJay · Accepted solution]

Changing the measure to:  

Sales Rank = RANKX(ALL(tblSales[Category]), CALCULATE(SUM(tblSales[Qty Sold]) ),,DESC,Dense)

Has fixed the issue.  (Still trying to work out why)

Got the solution from this excellent post.

https://powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/

[Zubair_Muhammad]

@BeeBeeJay

Try using ALLSELECTED

RANKX(ALLSELECTED(tblSales)

[BeeBeeJay]

Thanks for the suggestion, but this didn't work for me.