Using RANKX on a subcategory that's part of a table variable

[cferv_77]

Hello,

I have a table that I am trying to create a rank measure for. The table columns I am need to rank is based on [Category], [SubCategory], [SalesAmount]. I found this article that related to my situation and tried:

Ranking by Sub Category =
RANKX (
    FILTER(
       ALL( 'Table'[Category], 'Table'[SubCategory] ),
      'Table'[Category] = MAX('Table'[Category])
    ),
 CALCULATE(SUM('Table'[SalesAmount]))
)

However, it did not work. So I am thinking it is because of the duplicates, so I go group them by [Category] and [SubCategory] with a SUM on [SalesAmount]. I accomplish this using SUMMARIZECOLUMNS(). However, the solution does not seem to work with table variables. It seems like ALL() does not accept table variables.

So may I ask how I can accomplish this?

The end-goal is to use the measure within another measure (or if possible be part of this whole measure I am creating) to find the rank of a subcategory, such that the table structure looks like:

[Category] || [Sum of SalesAmount for that specific subcategory] || [That Subcategory_Rank for Each Category]

Category	Sum of SalesAmount for that ONE SubCategory	Rank for SubCategory in Each Category
A	51	8
B	879	2
C	673	4
D	520	6

For context, the category is a location, and the subcategory is a sales rep. Each sales rep may be found in all those locations. But I am focusing on just one sales rep’s performance, getting the rank for that person in each location.

Please and thank you.

EDIT: Apologies, I should have added that I cannot create a physical calculated table as base table is part of a SSAS and would prefer not to use DirectQuery if possible. 

[amitchandak]

@cferv_77 , When you display only by category you can not use subcategory rank

what you need

Ranking by Sub Category =
RANKX (
FILTER(
ALL( 'Table'[Category], 'Table'[SubCategory] ),
'Table'[Category] = MAX('Table'[Category])
),
CALCULATE(SUM('Table'[SalesAmount]))
)

and

Ranking by Category =
RANKX (
ALL( 'Table'[Category] ),
,
CALCULATE(SUM('Table'[SalesAmount]))
)

new rank = if(not(isinscope('Table'[SubCategory] )) && isinscope('Table'[Category] ) , [Ranking by Sub Category] ,[Ranking by Category] )

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

[cferv_77]

Hello @amitchandak ,

Thank you for your response. How would I handle the duplicates of category, subcategory, and/or combination of both within the table?

How/Where can I use a "groupby" function?

[v-chenwuz-msft · Accepted solution]

Hi @cferv_77 ,

I created an example file that

based on category ranking

sa of category = RANKX(ALL('Table'[Category]),CALCULATE(SUM('Table'[Sales])))

based on subcategory ranking

sa of subcategory = RANKX(ALL('Table'[SalesAmount]),calculate(SUM('Table'[Sales])))

Based on category and subcategory ranking

sa of all = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])))

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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