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

[v-sdhruv]

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

[v-sdhruv]

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

[v-sdhruv]

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

[Demert]

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

OneDrive Link 

[nish18_1990]

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

[Demert · Accepted solution]

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

[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

[nish18_1990]

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 . 

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

[nish18_1990]

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