Show TOP 250 Items as a Static Row

[Anonymous]

Hi community members, 

I am aware of adding a table with a few set of rows (disconnected and connected) to get the end results but I am not able to find any help to get a Top 250 Items table working. 

Problem: I am building a report for a retailer and they want to see the Top 250 Products sold within a selected date range across the entire company and with an associated Item Rank. This list can be filtered by product brand, category, segment , region etc..but not the retailers customers. What I mean by that is when the report user filters the Top 250 products table for a region, they will end up seeing the Top 250 products for their region but when it comes filtering on a customer, the list of products should always show the overall company's Top 250 products. 

The use case being, when their sales rep pulls up the report when interacting with his customer, he/she would like to know what in the Top 250 Items across the entire organization has been purchased or not purchased by that customer. 

Data model: The Sales fact table has a customer id, product id, brand id and region id with a many to one relationship to each of the dimensional tables. 

Thanks! 

Sumanth

[amitchandak · Accepted solution]

@Sumanthjb, typically if you created a Rank as a

Rankx(all(Table[prodcut]), [product sold])

It means product range within any other group by, if nothing then product. If you choose the company, then the top 250 within the product.

If you want to force any subcategory of specific use thin Range

See example in

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/3...

[Anonymous]

Thanks everyone for sending your response.

I ended up using a pre aggreated table of the sales from the last 12 months and creating the Rank function like you pointed out to get the final result.

Sumanth

[V-pazhen-msft · Accepted solution]

@Sumanthjb

Try to create a slicer to select the date range, and use the TOP n filter in the table visual to filter the top 250 product based on the sales value.

Paul Zheng _ Community Support Team
If this post helps, then consider Accept it as the solution to help other members find it faster.

[amitchandak · Accepted solution]

@Sumanthjb, typically if you created a Rank as a

Rankx(all(Table[prodcut]), [product sold])

It means product range within any other group by, if nothing then product. If you choose the company, then the top 250 within the product.

If you want to force any subcategory of specific use thin Range

See example in

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/3...

[NamishB]

Hi @Anonymous :

Can you share Sample data please? 

Thanks,

-Namish B