Sum of sales of top 10 products each year

[Anonymous]

Hi All,

I have columns as price, products, year..Now, i want to calculate sum of sales of top 10 products for eacyh year. How do i do that?Can anyone provide the correct dax measure to be used.

TIA

[PaulDBrown · Accepted solution]

@Anonymous 

It really depends on what you wish to display in your visuals. If it is just the sum fo the top 10, then the function TOPN is probably your best bet. Otherwise you have options of using RANKX or even just the filter pane.

Here are some examples for a simple dataset with values per item (in this case for the top 3 Items in terms of "Actuals"):

1) displaying the sum of the top 3 Items per year in a table/matrix:

With these measures: 

Sum of Actuals = SUM('Data Table'[Actuals])

Top 3 per year = SUMX(TOPN(3; ALLSELECTED('Item'[Item]); [Sum of Actuals]; DESC); [Sum of Actuals])

2) or you can use the filter pane to show the top N values (in this case top 3):

3) or you can use rhe RANKX function:

RankX Actuals (Item) = 
VAR calc = RANKX(ALLSELECTED('Item'[Item]);[Sum of Actuals];; DESC;Dense)
RETURN
IF(ISINSCOPE('Item'[Item]); calc; BLANK())

and then

Top 3 Items using RANKX = 
VAR calctotal = CALCULATE([Sum of Actuals]; 
                FILTER('Item';
                [RankX Actuals (Item)]<4))
VAR top3 = IF([RankX Actuals (Item)]<4; [Sum of Actuals]; BLANK())
Return
IF(ISINSCOPE('Item'[Item]); top3; calctotal)

To get you this