Skip to main content
cancel
Showing results for 
Search instead 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

Reply
srk_powerbi
Helper II
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
1Tvs
2Projectors
3Cables
4Phones
5Mounts
6Batteries
7Printers
8Home supplies
9Cameras
10Heaters

 

Sales

ProductID             Sales             Profit       Profit %
1120,000   35000      29%
256000  12000     21%
3230000  45000    20%
476000  21000    28%
548000 9000    19%
6130000 32000    25%
745000  4000    9%
812500024000   19%
914000017000   12%
1065000  11000  17%

 

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

Product Sales         Profit        Profit %
Cables230000  45000   20%
Cameras140000   17000   12%
Batteries130000   32000  25%
Home supplies125000  24000   19%
Tvs120,000    35000   29%

 

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

1 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:

Capture.PNG

 

Capture1.PNG

 

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

 

View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

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.

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.


Go to My LinkedIn Page


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

amitchandak
Super User
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

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)

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:

Capture.PNG

 

Capture1.PNG

 

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

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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