RANKX Confusion in Implicit Hierarchy

DAX DAX Calculations
1

I want to rank sales by value, at a Product level. This is working just fine, but when I add in the Product Subcategory, suddenly the ranks are all 1. For example:

RANKX_Product
RANKX_Product2531×978 239 KB

This is all great. But if I add Subcategory…

RANKX_ProductSubCategory
RANKX_ProductSubCategory2497×738 215 KB

Which is not what I wanted! As you can see I am using Contoso but my real life data is the same structure. I have attached the pbix. If someone could point out the error of my ways I would be most grateful! Thank you.

Contoso - Rank Question.pbix (4.4 MB)

2

@Jamie,

Give this a go:

Sales Value Rank = 
RANKX(ALLSELECTED(dimProductHierarchy[ProductSubCategoryName],dimProductHierarchy[ProductName]),
    [Total Sales]
    ,,DESC)

image

Just needed to fix the context and take out the unnecessary CALCULATE.

I hope this is helpful.

– Brian

3

Brilliant, thank you!

4

@Jamie,

:+1:- Glad to help.

  • Brian