calculate Top N and bottom N - help

[srk_powerbi]

hi,

i have a products table,,,,,and i created  three measures for  Sales, Profits, Profit % . I want to show top 5 and bottom 5 products by sales.  I created a measure and added in table , product and top 5 measure. it is showing correctly but i want to add another measures Profits, Profit % to the table also. But when i add them, the sales ranking does not work and table showing more than 5 products not Top 5.   Please help.

Updated Question:

@Jihwan_Kim   @amitchandak   let me givw you more details..this is what i have Product Table, Sales Table

they r joined by ProductID.

Product

ProductID	Product
1	Tvs
2	Projectors
3	Cables
4	Phones
5	Mounts
6	Batteries
7	Printers
8	Home supplies
9	Cameras
10	Heaters

Sales

ProductID	Sales	Profit	Profit %
1	120,000	35000	29%
2	56000	12000	21%
3	230000	45000	20%
4	76000	21000	28%
5	48000	9000	19%
6	130000	32000	25%
7	45000	4000	9%
8	125000	24000	19%
9	140000	17000	12%
10	65000	11000	17%

i want a table to  showTop5 Products by sales   and their Profit and Profit% like below..

Product	Sales	Profit	Profit %
Cables	230000	45000	20%
Cameras	140000	17000	12%
Batteries	130000	32000	25%
Home supplies	125000	24000	19%
Tvs	120,000	35000	29%

SImilarly i need another table showing Bottom 5 Products by sales, and their Profit and Profit%

[Jihwan_Kim]

Hi, @srk_powerbi 

Please try the below measures for your profit and profit%.

Profit top5sales =

IF( [Top5 Sales] <= 5, SUM('Table'[Profit]), BLANK())

Profit % = IF([Top5 Sales] <= 5, SUM('Table'[Profit %]), BLANK())

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[srk_powerbi]

hi @Jihwan_Kim      this is not working ...i wnat to show top 5 sales and their corresponding Profit and Profit % in same table.

[amitchandak]

@srk_powerbi , If you have created a Rank, best is to use that as a visual level filter, it will apply for that visual

Otherwise all the measures have to follow TOPN

CALCULATE([Sales],TOPN(5,all(Table[product]),[Tot_Cost],DESC),VALUES(Table[product]))

CALCULATE([Profit],TOPN(5,all(Table[product]),[Tot_Cost],DESC),VALUES(Table[product]))

You can refer this video: https://youtu.be/QIVEFp-QiOk

[srk_powerbi]

hi @amitchandak ,

I tried but not working, when add filter, the rank column is showing same rank for multiple rows,i am using this for Rank

RANKX(ALL(Table1[Product]),CALCULATE(SUM(Table[Sales])),,DESC)

[v-deddai1-msft · Accepted solution]

Hi @srk_powerbi ,

You can create two measures for visual level filter:

Measure = IF(MAX(Sales[ProductID]) in  CALCULATETABLE(VALUES(Sales[ProductID]),TOPN(5,SUMMARIZE(ALL(Sales),Sales[ProductID],"_sales",[Sales Measure]),[_sales])),1,0)

Measure 2 = IF(MAX(Sales[ProductID]) in  CALCULATETABLE(VALUES(Sales[ProductID]),TOPN(5,SUMMARIZE(ALL(Sales),Sales[ProductID],"_sales",[Sales Measure]),[_sales],ASC)),1,0)

And set them to 1 in filter pane:

Please refer to the attached file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai