Top N filtering across multiple dimensions

[RM2020]

I am using Top N filtering in my dashboard and I want to create a Matrix with sales item as the columns, year as the rows and number of sales as the values. I will be filtering by top sales amounts.

In the example of a Top 10 filter, I want the contents of  the entire table to add up to no more than 10 but currently I get the top 10 for each year split by sales item. My totals are also incorrect.

To make the matrix below I have used the following formula for each of the sales items. I then put the 3 formulas into the matrix as the values.

TopN Total Sales Books = CALCULATE([Total Sales], TOPN([SelectedTopNValue], 'Sales', [Total Sales], DESC, 'Sales'[Sale ID]),Filter('Sales,[Sales Item]="Books"))

Year	Books	Toys	Shoes
2019	3	0	7
2020	1	0	9
2021	3	0	7
2022	1	0	9
Total	2	0	8

If I disregard the formula above and use the TopN Total Sales I get the Top 10 for each Sales Items each year as seen below.

TopN Total Sales = CALCULATE([Total Sales], TOPN([SelectedTopNValue],'Sales', [Total Sales], DESC, 'Sales'[Sale ID]))

Year	Books	Toys	Shoes
2019	10	5	5
2020	10	2	3
2021	10	7	8
2022	10	8	4
Total	10	10	10

I just want to see how my Top 10 sales are split into each category in the example shown below.

Year	Books	Toys	Shoes
2019	2	0	1
2020	0	1	0
2021	1	0	1
2022	1	1	2
Total	4	2	4

Any ideas?

[Mariusz · Accepted solution]

Hi @RM2020 

Try something like this.

Top N sales by Year and Color = 
VAR __selection =
    MAX ( '# Top N Selection'[Select Top N] )    
VAR __groupBy =
    CROSSJOIN ( ALL ( 'Product'[Color] ), ALL ( 'Calendar'[Year] ) )
VAR __topN =
    TOPN ( __selection, __groupBy, [Sales], DESC )
RETURN
    CALCULATE ( [Sales], KEEPFILTERS ( __topN ) )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RM2020]

That worked great. Thank you so much!!!