topic Re: RANKX formula confusion in DAX Commands and Tips

RANKX formula confusion

rahulmmenon — Sun, 15 Jan 2023 12:17:43 GMT

Dear Team,

I have a dataset in which Category, Sub Category and Sales are given.

I wish to create a matrix in which i want to see RANK based Category and Sub category when selected.

I have created one Fact table and 2 Dimention tables (Category table & Sub category table) and established relationships.

I want to see RANKS of each category in Matrix and while expanding the category button, rank should modify based on Sub category within same category.

Please note: sales column is from "Fact table", Sub category column from "Sub category table" and Category column from "Category table" as shown below.

Re: RANKX formula confusion

Jihwan_Kim — Sun, 15 Jan 2023 15:00:52 GMT

Hi,

I suggest creating a data model like below.

Sales measure: = SUM( Data[Sales] )

Ranking: = SWITCH ( TRUE (), ISINSCOPE ( 'Sub Category'[Sub Category] ), RANKX ( ALL ( 'Sub Category'[Sub Category] ), [Sales measure:],, DESC ), ISINSCOPE ( 'Sub Category'[Category] ), RANKX ( ALL ( 'Sub Category'[Category] ), [Sales measure:],, DESC ) )

Re: RANKX formula confusion

rahulmmenon — Tue, 17 Jan 2023 03:09:23 GMT

Hi Jihwan,

thanks for your support on this. This gives a solution for my problem when the Category and Sub category columns are in same Dimention table. I actually wanted to do the same where Category and Sub category are two different Dimention tables.

But, since this gives a solution, i would like to go ahead with this.

thanks once again for your support.