Forum Discussion

didi42's avatar
didi42
Frequent Visitor
4 years ago
Solved

TOPN across several measures

 

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
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.