Top N parameter based on the totals in matrix

[nish18_1990]

Hi ,

I need a top N parameter based on the count of Sales .  If i select top 2 then it should show me top 2 subcategory based on the total Sales which is displayed on the right side .

Data:

Category	Sub Category	Sales	Quantity Q2	country
Furniture	Chairs	100	10	China
Furniture	Tables	100	20	China
Automobile	Cars	100	30	China
Automobile	Bikes	700	40	China
Furniture	Chairs	100	13	India
Furniture	Tables	500	22	India
Automobile	Cars	100	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	300	29	China
Automobile	Cars	200	88	China
Automobile	Bikes	100	22	China
Furniture	Chairs	500	21	India
Furniture	Tables	100	33	India
Automobile	Cars	600	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	100	29	China
Furniture	Sofa	100	88	China
Furniture	Bed	100	22	China
Furniture	Dining	100	21	India
Furniture	Tables	100	33	India
Furniture	Tables	100	54	India
Furniture	Sofa	100	60	India
Automobile	Cars	1000	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	200	29	China
Furniture	Sofa	100	88	China
Furniture	Bed	100	22	China
Furniture	Dining	100	21	India
Furniture	Tables	400	33	India
Furniture	Tables	800	54	India
Furniture	Sofa	100	60	India

[Nasif_Azam]

Hey @nish18_1990 ,

To implement a Top N parameter based on the total sales in your matrix, you can follow these steps:

1. Create a Measure for Total Sales: Create a DAX measure to sum up the Sales column for each subcategory. 

   TotalSales = SUM('YourTable'[Sales])

2. Create a Top N Filter Using DAX: You can create a dynamic measure that calculates the rank of each subcategory based on TotalSales and then filters to show only the Top N subcategories. 

   RankBySales = RANKX(ALL('YourTable'[Sub Category]), [TotalSales], , DESC, Dense)

   This RankBySales measure will rank the subcategories based on total sales in descending order. The Dense argument ensures that if multiple subcategories have the same total sales, they will get the same rank.

3. Apply the Top N Filter: To apply a Top N filter, create a slicer allowing users to select the number of subcategories they want to view (e.g., Top 2). Then, add the RankBySales measure to the Filters on this visual pane in the matrix. Set the filter condition to show subcategories where the RankBySales measure is less than or equal to the selected Top N value.

For Detailed Information:

RANKX Function in DAX

Using Power BI Slicers

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Demert]

Hi

you can create a new measure Called TotalSales with the following code: 

CALCULATE(count('Table'[Sales]),REMOVEFILTERS('Table'[country]))

and change 'Table" to your table name. This will calculate the total of all the sales by removing the filter of countries that is automatically applied by the matrix visual. Next up you can drag this measure into your Filter pane view and filter the Sub category

 

[nish18_1990]

I need a dynamic parameter selection for Top N selection as well. 

[Demert]

Hi,

What you can do is add a numeric field parameter as input fro your dynamic parameter selection. After that you need to create a new measure to calculate the ranking. And determine if the ranking is above or under the selected Top N.

Here is an example: 

RankBySales =

VAR _SelectedRank = SELECTEDVALUE(Parameter[Parameter])

VAR _Ranking = RANKX(ALL('Table'[Sub Category]), [TotalSales], , DESC,Skip)

RETURN IF( _Ranking > _SelectedRank , BLANK(),COUNT('Table'[Sales]))