cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## calculate Top N and bottom N - help

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%

1 ACCEPTED SOLUTION
Community Support

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

5 REPLIES 5
Super User

Hi, @srk_powerbi

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

Helper II

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

Super User

@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

Helper II

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)

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors