Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Creating top n in same report page based on filter

Hi All,

I have a report with various visuals along with top 10 materials based on inventory qty. so, lets say if i click on particular plant in same page, top 10 just highlight only material that falls under top 10 list. it could be one material or more. similarly another chart on the same page has another category and , clicking on that only highlight corresponding material .

I want that, if i click on particular pant on chart, top should display all top 10 material based on that plant, similarly if on another plant then top 10 for another plant. same behaviour for category.
Please help.
Manveer

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Amit, I have tried like below and seems working, thank you

test ranking sh+tr =
if (HASONEVALUE('abc'[Plant Desc]),
var Plant_list =IF(HASONEVALUE('abc'[Plant Desc] ),RANKX(ALLSELECTED('abcc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abc',Plant_list<=10)),

if(HASONEVALUE('abc'[Brand Group]),

var Brand_list =if(HASONEVALUE(abc'[Brand Group]),RANKX(ALLSELECTED('abc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abcy',Brand_list<=10)),
if(HASONEVALUE('abc'[Product family Group]),

var ProductFam_list =if(HASONEVALUE('abc'[Product family Group]),RANKX(ALLSELECTED('abc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abcy',ProductFam_list<=10)),
[#top 10 Material scheduled +Transit])))

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , As far as what I got rank on Material should do that or top.

Can you share sample data and sample output in table format?

example

= CALCULATE([measure],TOPN(10,all(Table[material]),[measure],DESC),VALUES(Table[material]))

 

Rank = RANKX(all(Table[material]),[measure])

 or like  sumx(VALUES(Table[material]),if([Rank]<=10,[material],BLANK()) )

 

In case what only 10 and you are getting more than that you can add a very small random number and make sure they are only 10

You can also refer

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak , i want that whn i selected plant in first graph, my top 10 char shoul give me material based on slected plan and not just highlight as shown below. hope this is more clear. thank you

formula i have used: 

#top 10 Material Transit =

var rak= rankx(all('abc'),[#Quantity_Stockout WOS+ transit],,desc,skip)
return

CALCULATE([#Quantity_Stockout WOS+ transit],FILTER('abc',rak<=10))

ManveerN_0-1599796123178.png

 

Anonymous
Not applicable

Hi Amit, I have tried like below and seems working, thank you

test ranking sh+tr =
if (HASONEVALUE('abc'[Plant Desc]),
var Plant_list =IF(HASONEVALUE('abc'[Plant Desc] ),RANKX(ALLSELECTED('abcc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abc',Plant_list<=10)),

if(HASONEVALUE('abc'[Brand Group]),

var Brand_list =if(HASONEVALUE(abc'[Brand Group]),RANKX(ALLSELECTED('abc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abcy',Brand_list<=10)),
if(HASONEVALUE('abc'[Product family Group]),

var ProductFam_list =if(HASONEVALUE('abc'[Product family Group]),RANKX(ALLSELECTED('abc'[Material Key]), [#Quantity_Stockout WOS+TR+SH],,0))
return
CALCULATE([#Quantity_Stockout WOS+TR+SH],FILTER('abcy',ProductFam_list<=10)),
[#top 10 Material scheduled +Transit])))

Anonymous
Not applicable

@amitchandak could you please suggest

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.