topic Re: Top N parameter based on the totals in matrix in DAX Commands and Tips

Top N parameter based on the totals in matrix

nish18_1990 — Fri, 20 Jun 2025 13:21:15 GMT

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

Re: Top N parameter based on the totals in matrix

Demert — Fri, 20 Jun 2025 13:35:00 GMT

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

Re: Top N parameter based on the totals in matrix

nish18_1990 — Fri, 20 Jun 2025 13:56:55 GMT

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

Re: Top N parameter based on the totals in matrix

Nasif_Azam — Fri, 20 Jun 2025 14:26:40 GMT

Hey nish18_1990 ,

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

Create a Measure for Total Sales: Create a DAX measure to sum up the Sales column for each subcategory.
```
TotalSales = SUM('YourTable'[Sales])
```
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.
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

Re: Top N parameter based on the totals in matrix

Demert — Fri, 20 Jun 2025 14:44:34 GMT

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]))

Re: Top N parameter based on the totals in matrix

nish18_1990 — Mon, 23 Jun 2025 05:46:34 GMT

I need to do it in Count of sales . My measure is count . How can i calcualte the sum of the total of count of measure .

Re: Top N parameter based on the totals in matrix

nish18_1990 — Mon, 23 Jun 2025 06:33:35 GMT

Could you please provide me the power bi .I tried and its not working as expected .

Re: Top N parameter based on the totals in matrix

Demert — Mon, 23 Jun 2025 07:13:27 GMT

Hi, Sure. You can find it through the following link:

OneDrive Link

Re: Top N parameter based on the totals in matrix

nish18_1990 — Mon, 23 Jun 2025 08:25:29 GMT

Could you please attach the file as i am unabe to open the one drive due to some restricted permission.

Re: Top N parameter based on the totals in matrix

Demert — Mon, 23 Jun 2025 08:51:13 GMT

Hi I can't attach the file yet to my replies: try the following we transfer link :

Download

The steps I did:

Create in the modeling view select new parameter and select numeric range. Minimum 1 and max 10 with increments of 1 and add to page.

Then create a table visual and drag the subcategory into it. Create a new measure to calculate the total sales and do the ranking based on the total sales

TotalSales = CALCULATE(count('Table'[Sales]),REMOVEFILTERS('Table'[country])) RankBySales = VAR _SelectedRank = SELECTEDVALUE(Parameter[Parameter]) VAR _Ranking = RANKX(ALL('Table'[Sub Category]), [TotalSales], , DESC,Skip) RETURN IF( _Ranking > _SelectedRank , BLANK(),COUNT('Table'[Sales]))

Also to check if the selected ranking in the parameter is lower than the rank of the subcategory

Re: Top N parameter based on the totals in matrix

v-sdhruv — Mon, 07 Jul 2025 07:24:02 GMT

Hi nish18_1990 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
Thank you Demert and Nasif_Azam for your prompt response to the query.
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank You

Re: Top N parameter based on the totals in matrix

v-sdhruv — Wed, 02 Jul 2025 05:35:23 GMT

Hi @nish18_1990 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank You

Re: Top N parameter based on the totals in matrix

v-sdhruv — Mon, 07 Jul 2025 05:28:32 GMT