RANKX within groups without creating multiple tables

Question

Hi all,&nbsp;&nbsp;I'm struggeling to make a RANKX measure in which I rank sales value of different brands.&nbsp;&nbsp;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.&nbsp;&nbsp;Any ideas are much appreciated.&nbsp;&nbsp;CategorySubcategoryBrand&nbsp;SubbrandSales ValueDesired RANKX outcomeFacecareCleansingAngelo&nbsp;Joanna&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.1001FacecareCleansingLucie&nbsp;Mandy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7.4004FacecareCleansingMarco&nbsp;Hannah&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.8005FacecareCleansingMaximilian&nbsp;Udo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.5002FacecareCleansingRoberto&nbsp;Elmar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8007FacecareMoisturizingAngelo&nbsp;Heinz-Peter&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.3001FacecareMoisturizingLucie&nbsp;Hans-Walter&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.0004FacecareMoisturizingMaren&nbsp;Dietrich&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.1003FacecareMoisturizingMaximilian&nbsp;Christina&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.3002FacecareMoisturizingOtmar&nbsp;Ekkehard&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.6006SuncareAftersunAndré&nbsp;Anita&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.5005SuncareAftersunMaximilian&nbsp;Michaela&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.8002SuncareAftersunKornelia&nbsp;Heiderose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.1006SuncareAftersunNicolas&nbsp;Annegret&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4001SuncareSPFDorothee&nbsp;Dora&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.9003SuncareSPFGabriel&nbsp;Lieselotte&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.6004SuncareSPFMaximilian&nbsp;Carola&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.9002SuncareSPFMaik&nbsp;Sabine&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.1008SuncareSPFNicolas&nbsp;Aloisia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.3001SuncareSPFRose&nbsp;Maja&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4009SuncareSPFUlli&nbsp;Elsbeth&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.6007&nbsp;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.&nbsp;&nbsp;Thanks once again!

parry2k · Accepted Answer

Anonymous&nbsp;there are amazing posts on this, take a look here
&nbsp;
Showing the top 5 products and Other row - SQLBI
&nbsp;
Filtering the Top 3 products for each category in Power BI - SQLBI
&nbsp;
✨ Follow us on LinkedIn 
&nbsp;
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤&nbsp;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! 
&nbsp;
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Forum Discussion

RANKX within groups without creating multiple tables

2 Replies

Related content

Creating multiple Audiences in PowerBI App

Multiple messages when new item created

Creating Multiple manage relationships at ones

How to avoid people creating multiple times the same table in different workspaces

MULTIPLE CARD

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