Creating a Top N and Others Table from related table (using Rankx)

[Anonymous]

Hello,

I'm trying to create a table showing say Top 5 Sales by Products and the balance in others. My tables are as follows:

SalesTable
Customer	Product	Amount
A	AAA	100
B	BBB	50
C	CCC	10
D	AAA	100

ProductsTable
Product	Description
AAA
BBB
CCC

Intended output...

Ranked Product Table
Product	Sales
AAA	200
BBB	50
Others	10

I have tried the solution here, but came across errors.
I am able to rank the products with the following formula:
Product Rank = RANKX(ALL('ProductsTable'[product]),SUMX(RELATEDTABLE('SalesTable'),'SalesTable'[Amount]))

I am unable to create the next formula:
Top 5 products = IF([Product Rank]<6,'ProductsTable'[product],"others") where i am unable to reference the column, 'ProductsTable'[product], either directly or using RELATED.

Does anyone know how to solve this?

[amitchandak]

@Anonymous , try like

rank1 =RANKX(ALL('ProductsTable'[product]),calculate(SUM('SalesTable'[Amount])))

calculate(sum('SalesTable'[Amount]),filter('SalesTable',[rank1]<=5))

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

[harshnathani]

Hi @Anonymous ,

You can have a look at the blog.

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

https://www.youtube.com/watch?v=UAnylK9bm1I

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)