RANKX within groups without creating multiple tables

[Anonymous]

Hi all, 

I'm struggeling to make a RANKX measure in which I rank sales value of different brands. 

In the fictional table below I want to rank Brand by Category ignoring the columns subcategory and subbrand. I tried things like allselected, values, all, summarize columns but nothing gives the right answer. I do not want to use seperate tables per category because the report needs all categories within the same slicer and I need the columns that are ignored. 

Any ideas are much appreciated. 

Category	Subcategory	Brand	Subbrand	Sales Value	Desired RANKX outcome
Facecare	Cleansing	Angelo	Joanna	7.100	1
Facecare	Cleansing	Lucie	Mandy	7.400	4
Facecare	Cleansing	Marco	Hannah	6.800	5
Facecare	Cleansing	Maximilian	Udo	8.500	2
Facecare	Cleansing	Roberto	Elmar	800	7
Facecare	Moisturizing	Angelo	Heinz-Peter	9.300	1
Facecare	Moisturizing	Lucie	Hans-Walter	1.000	4
Facecare	Moisturizing	Maren	Dietrich	9.100	3
Facecare	Moisturizing	Maximilian	Christina	4.300	2
Facecare	Moisturizing	Otmar	Ekkehard	6.600	6
Suncare	Aftersun	André	Anita	5.500	5
Suncare	Aftersun	Maximilian	Michaela	2.800	2
Suncare	Aftersun	Kornelia	Heiderose	5.100	6
Suncare	Aftersun	Nicolas	Annegret	400	1
Suncare	SPF	Dorothee	Dora	5.900	3
Suncare	SPF	Gabriel	Lieselotte	5.600	4
Suncare	SPF	Maximilian	Carola	3.900	2
Suncare	SPF	Maik	Sabine	1.100	8
Suncare	SPF	Nicolas	Aloisia	8.300	1
Suncare	SPF	Rose	Maja	400	9
Suncare	SPF	Ulli	Elsbeth	2.600	7

For extra bonus points 😉 : later I want to use the rank outcome to create a measure giving the brand name of the top 5 ranked brands and all other brands to be called "other" which have to go in the rows of a matrix table visual. This might be important for any answer. 

Thanks once again!

[parry2k · Accepted solution]

@Anonymous there are amazing posts on this, take a look here

Showing the top 5 products and Other row - SQLBI

Filtering the Top 3 products for each category in Power BI - SQLBI

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k thanks so much for your help! I've used the first article that you've posted but I'm stuck again.

Referring to an example of the article itself;

I would like to do a calculation where the share of sales value ranking name (or product name) is divided by total sales value of all filtered subcategories. However, when I create a calculated table to include the subcategory the top N is calculated incorrectly since the ranking name is not unique anymore. Do you have any pointers how to go about on this?