Rankx for a specific category within a group

[natksi]

Hi, I am trying to get the following rank, but somehow my dax doesn't work.

Company	Category	Sales	Rank
A	A	1000	1
A	B	500	3
B	A	999	2
B	B	450	4

it should rank category among category A &B. 

I have tried:

rankx(all([company], [category]),

sum([sales]))

and variations with calculated columns.

Please help, thanks

[natksi]

hi @amitchandak, thanks for your previous reply!

How do you deal with rankx if [category] field is filtered in the visual? Would not need to rank all categories as in
rankx(all(Table[company], Table[category]),calculate( sum([sales])),,desc, dense), just the ones filtered for in the visual

[natksi]

Somehow the solution suggested doesn't work.

I think it is because [ Category] is filtered in my visual. So here, we want to rank companies in only 2 categories from the table. How do we achieve that in DAX? 

[amitchandak · Accepted solution]

@natksi , A new measure

rankx(all(Table[company], Table[category]),calculate( sum([sales])),,desc, dense)

or

 a new column

Rankx(Table, [Sales],,desc, dense)