Summarized Ranking with RLS

[Kniggit270]

Hello all,

Really hoping someone can help as I haven't quite found the same scenario in other posts unfortunately.

I have the following dataset

Sale ID	Sale Date	Product Category	Manufacturer	Country	Colour	Sales Quantity	Amount
001	05/04/2022	Lights	A	UK	Blue	354	£               630
002	08/06/2022	Sofas	A	UK	Red	74	£            3,388
003	12/04/2022	Cushions	A	UK	Red	154	£               126
004	02/03/2023	Curtains	A	USA	Blue	90	£               363
005	08/01/2023	Wallpaper	A	France	Yellow	795	£               453
006	19/12/2022	Lights	B	Italy	Green	200	£               334
007	03/12/2022	Sofas	B	UK	Red	25	£               656
008	22/06/2022	Cushions	B	USA	Yellow	194	£                 70
009	14/03/2023	Curtains	B	UK	Blue	38	£               232
010	24/02/2023	Wallpaper	B	UK	Green	264	£               132
011	14/08/2022	Lights	C	Italy	Red	398	£               708
012	01/11/2022	Sofas	C	Italy	Blue	49	£            1,399
013	15/05/2022	Cushions	C	France	Green	206	£               165
014	03/07/2022	Curtains	C	Italy	Red	79	£               588
015	03/05/2022	Wallpaper	C	UK	Yellow	246	£                 76
016	05/09/2022	Lights	D	USA	Blue	231	£               413
017	12/08/2022	Sofas	D	USA	Blue	57	£            1,329
018	01/03/2023	Cushions	D	USA	Red	106	£                 52
019	14/09/2022	Curtains	D	Italy	Green	97	£               529
020	27/11/2022	Wallpaper	D	UK	Red	549	£               176

Which I am using for a number of different visuals across a report with RLS for Manufacturer.

The Manufacturer can see their performance over set period of time, YoY performance, country map, lots of things.

What I would like is to have ranking for the Manufacturer against it's peers acrss the different categories, and also as an overall sales amount.

As an example, with RLS enabled for a Manufacturer, they could see the following:

Manufacturer Rank (I will anonomise the names for the one not selected)

Rank	Manufacturer	Amount	Total	% of Total
1	B	£           1,424	£         11,819	12%
2	A	£           4,961	£         11,819	42%
3	C	£           2,936	£         11,819	25%
4	D	£           2,499	£         11,819	21%

Country Rank

Country	Rank	Amount	Total	% of Total
USA	3	£              363	£           2,227	16%
UK	1	£           4,145	£           5,416	77%
France	2	£              453	£              618	73%
Italy	4	£                 -	£           3,558	0%

Product Rank

Country	Rank	Amount	Total	% of Total
Lights	3	£              630	£           2,086	30%
Sofas	1	£           3,388	£           6,772	50%
Cushions	2	£              126	£              413	31%
Wallpaper	4	£              453	£              837	54%

Would be great as well if for example, they selected a Country in the Country Rank table, and it filtered to the Product Rank table and the Manufacturer Rank table to give even more insight.

Many thanks to anyone that can help 🙂

[Kniggit270]

So I've managed to partially do it thanks to this article: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

However, the issue is, in order to acheive that, I have had to create new tables using the summarize() feature, to then use the countrows() for the ranking as it doesn't seem to work directly on the raw data. I've then created a separate table for Manufacturer which links to the variouse summary tables (one for each category).

What this doesn't allow for is cross filtering between categories though which really is a must.

I've so far got to this which allows for the cross filtering but the ranking doesn't quite come out right:

Product Ranking =

COUNTROWS(

          FILTER(

               SUMMARIZE(

                     FILTER(ALL('Sales'),'Sales'[Product] = MAX('Sales'[Product])),'Manufacturer'[Manufacturer],"Value",[Total Sales]),[Value]>=[Total Sales]))

With [Total Sales] as a Measure for the sum of the sales amount, and a new table with the unqiue Manufacturers names in

Any thoughts?