RankX at Category level for Category/SubCategory context

[Anonymous]

Hi - looking for some help with a RankX DAX calculation.  For illustrative purposes, I'm using a simple table with Category, SubCategory, and Value.  I'd like to create a measure that ranks by Category for total value across all SubCategory.  

Currently, the measure is:

CategoryRank = RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Value])))

This works when SubCategory is excluded. However, when I include SubCategory, it evaluates across the unique combinations.

What I'd like to return is a table like:

CategoryRank Category SubCategory Value
4 D D1 9,936

1 A A4 9,507

2 B B1 9,446

etc

pbix drobbox link

[v-deddai1-msft]

Hi @Anonymous ,

According to your description, my understanding is that you want to rank your data by category and subcategory in a measure. If anything is misunderstood, please tell me.

Would you please create a measure to determine whether it is a Category or SubCategory?

ISCategory = COUNTROWS('Table') = CALCULATE(COUNTROWS('Table'),ALL('Table'), VALUES('Table'[Category]))

Then create a measure for ranking:

CategoryRank = IF( [ISCategory], RANKX( ALLSELECTED('Table'[Category]),CALCULATE(SUM('Table'[Value]))), RANKX(ALLSELECTED('Table'[SubCategory]),CALCULATE(SUM('Table'[Value]))))

For more details, please refer to https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ee2raMYlhUJEk0MhAdVhoEgB2k-iqHNU-NGNKfNW2a_6EA?e=DI5ibg

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Ashish_Mathur]

Hi,

I am not sure of what you want but see if my solution in this file helps.

[123abc]

Ashish Sir,

I also need this solution for my office assigment please share the DAX formula. In my assinnments there area 5 categoriues like 2 Regions, 7 Zones, 24 Branches and 45 Area Sales Officers, i want to making ranking from between Retions, then Zones, then brnahes then area sales officers... please guide. 

[Ashish_Mathur]

Hi,

I do not have that file now.  Share some data and show the expected result.

[123abc]

Dear Sir,

Reference data is attached.

Thank You so much for support.

https://drive.google.com/file/d/18ULxB7JdQXjqQszXQHw9BQ28lazJsD5X/view?usp=sharing

[Ashish_Mathur]

Share the download link of Excel and/or PBI file and show the expected result clearly.

[123abc]

Dear Sir,

Reference data is attached.

Thank You so much for support.

https://drive.google.com/file/d/18ULxB7JdQXjqQszXQHw9BQ28lazJsD5X/view?usp=sharing

[HotChilli · Accepted solution]

A measure like this should work

CategoryRank = RANKX(ALL('Table'[Category]), CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table','Table'[Category])))

[Anonymous]

Thank you very much! Definitely wasted too much time trying to figure this one out!