TOPN across several measures

[didi42]

Hello,

I would need your help on one issue I am able to solve in m language but I would like to do in DAX : 

• I have a simple data set of sales of product per store and per dates, as well as text comment for each product sale

• This set is linked to a calendar table created with CALENDAR() function
• I have measures to return the monthly variation of each product

Previousmonth variation product2 = SUM(Sheet1[Product 2])-calculate(sum(Sheet1[Product 2]),PREVIOUSMONTH('Calendar'[Date].[Date]))

I am able to display the TOPN variation of each product individually by applying TOPN filter on my Table, however, I would like to get the TOPN for all the products altogether. For example, for February, I would like to get for TOP 5 variations :

I found a way to do it in m language, but  guess it would be less time processing and more evolutive by doing it in DAX.

For information, the way I can do it in m is:

• Add a new column which return the date of the previous month
• Join the table with itself with the 2 dates columns, I then get on 1 row the data of current month and previous month
• Add new columns to get variations for each product
• Use List.Zip to get the columns I need as shown in the previous example table

[v-yalanwu-msft · Accepted solution]

Hi, @didi42 ;

You could try to create a table.

New table = 
var _a=SUMMARIZE('Table',[Store],[Explanation],"Variation",[Product 1],"Product","Product1")
var _b=SUMMARIZE('Table',[Store],[Explanation],"Variation",[Product2],"Product","Product2")
var _c=SUMMARIZE('Table',[Store],[Explanation],"Variation",[Product3],"Product","Product3")
return TOPN(5, union(_a,_b,_c),[Variation],DESC)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Can you share some actual sample data to work on?