topic Re: Display top 10 product without using dense and skip ranking in DAX Commands and Tips

Display top 10 product without using dense and skip ranking

DAXUser — Tue, 27 Dec 2022 10:15:29 GMT

Hi,

Product	Total Count	Rank	Expected Output
Screw	69	1	1
Spanners	68	2	2
Clutch	68	2	3
Cross	67	3	4
External Hex	66	4	5
Hex Socket	65	5	6
Square Socket	64	6	7
Tamper Resistant Hex	63	7	8
Torx	62	8	9
Slotted	61	9	10
One way	60	10	11
Plain	60	10	12
JIS	60	10	13

As you can see, I have a total count for each product in the table. I used the dense ranking based on the number of products. Now my requirement is I want to display first 10 products from the table which means I want to display continues number(1 to 10) similar to expected output column so that I will apply a filter on top of expected output column to get first 10 products.

Re: Display top 10 product without using dense and skip ranking

amitchandak — Tue, 27 Dec 2022 11:56:01 GMT

DAXUser , if this for meausre refer tie breaker

Rank Tie breaker
https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-Columns/ba-p/918655
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

But if this column, then consider

Power Query Table.AddRankColumn : https://youtu.be/bnB1rgAhZaw

Re: Display top 10 product without using dense and skip ranking

tamerj1 — Tue, 27 Dec 2022 12:23:05 GMT

Hi DAXUser
Please refer to attached sample file. You can use TOPN function in this case as folows (unless you need to show the rank in the visual):

Place the filter measure in the filter pane of the visual, select "is not blank" then apply the filter.

Top 10 Filter = IF ( SELECTEDVALUE ( Products[Product] ) IN TOPN ( 10, ALLSELECTED ( Products[Product] ), [Total Count],, Products[Product] ), 1 )