cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors