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

[Jinxz_21]

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]))

)

[Aburar_123 · Accepted solution]

Hi @Jinxz_21 ,

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

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,

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

[TomMartens]

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