Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jinxz_21
Regular Visitor

how to show top 3 total product sales and sales of the top 3 on all years

how to show top 3 total product sales and sales of the top 3 on all years?

i will be able to display the top 3 total sales
example 
Prod             Total
-a                 1000
   yr1             998  <- yr2 = 1 and yr3 = 1 dont display cause it is not on top3 on those 2 years
-b                  800
   yr2               800
-c                   750
   yr1              700
   yr3                50

is it possible to display it even though it is not part of top3 on those 2 years?

TopN Value =
var selectedtop = SELECTEDVALUE('TopN'[TopN])
var selectedyr = SELECTEDVALUE('Calendar'[Year])
var f =
    RANKX(ALLSELECTED('prod'['pid']),CALCULATE(SUM('sales'[Sales])),,DESC,Dense)
RETURN
IF(f<=selectedtop,
    CALCULATE(SUM(sales[Sales]))
)



1 ACCEPTED SOLUTION
Aburar_123
Resolver IV
Resolver IV

Hi @Jinxz_21 ,

 

 

Based on your information i assume your table data looks similar to below .

Aburar_123_0-1667024925097.png

 

So, you can write the measure like below and place it in the visual filter in matrix and make it as 1,

 

Top3 =

var top3_prods = TOPN(3,SUMMARIZE(all('Table'),'Table'[Product],"@Sales",sum('Table'[Sales])),[@Sales],DESC)
var filtered_Prods = selectcolumns(top3_prods,"product",'Table'[Product])
var top3_yrs = TOPN(3,SUMMARIZE(FILTER(all('Table'),'Table'[Product] in filtered_Prods)
                                            ,'Table'[Year],"@Sales",sum('Table'[Sales])),[@Sales],DESC)
var filtered_yrs = SELECTCOLUMNS(top3_yrs,"Yrs",'Table'[Year])

return if(max('Table'[Product]) in filtered_Prods && MAX('Table'[Year]) in filtered_yrs,1,0)
 
Below is your expected result,
 
Aburar_123_1-1667025244646.png

 

Please accept it as a solution if it satisfy your requirement. Thanks.

 

 

View solution in original post

2 REPLIES 2
Aburar_123
Resolver IV
Resolver IV

Hi @Jinxz_21 ,

 

 

Based on your information i assume your table data looks similar to below .

Aburar_123_0-1667024925097.png

 

So, you can write the measure like below and place it in the visual filter in matrix and make it as 1,

 

Top3 =

var top3_prods = TOPN(3,SUMMARIZE(all('Table'),'Table'[Product],"@Sales",sum('Table'[Sales])),[@Sales],DESC)
var filtered_Prods = selectcolumns(top3_prods,"product",'Table'[Product])
var top3_yrs = TOPN(3,SUMMARIZE(FILTER(all('Table'),'Table'[Product] in filtered_Prods)
                                            ,'Table'[Year],"@Sales",sum('Table'[Sales])),[@Sales],DESC)
var filtered_yrs = SELECTCOLUMNS(top3_yrs,"Yrs",'Table'[Year])

return if(max('Table'[Product]) in filtered_Prods && MAX('Table'[Year]) in filtered_yrs,1,0)
 
Below is your expected result,
 
Aburar_123_1-1667025244646.png

 

Please accept it as a solution if it satisfy your requirement. Thanks.

 

 

TomMartens
Super User
Super User

Hey @Jinxz_21 ,

 

I'm missing the following information, what data is there and what do you want to visualize.

Consider creating a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the file to onedrive, googledrive, or dropbox and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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