Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power Bi and DaX gurus,
I have a table with the daily sales of two products (table1). How can I generate another table (table2) that gives me the monthly average sales for both product in a single table? I used the “groupby” DaX function but it generates a table with only one product. Below is my table and the result table that I would like to get. Any tips or help on the DaX formula?
Table1
Date | Sales_A | Sales_B |
5/15/2018 | 20 | 10 |
5/16/2018 | 30 | 20 |
6/15/2018 | 10 | 40 |
6/16/2018 | 20 | 20 |
Table2
Date | Average Sales_A | Average Sales_B |
May 2018 | 50 | 15 |
June 2018 | 15 | 30 |
Thanks for your help
Solved! Go to Solution.
First, you need to add a calculated column to you first table:
Month = MONTH([Date])
Then,
Table = SUMMARIZE('Table',[Month],"Average Sales_A",AVERAGE([Sales_A]),"Average Sales_B",AVERAGE([Sales_B]))
Sure it would, you just keep adding columns, you can add as many columns to summarize as you wish.
First, you need to add a calculated column to you first table:
Month = MONTH([Date])
Then,
Table = SUMMARIZE('Table',[Month],"Average Sales_A",AVERAGE([Sales_A]),"Average Sales_B",AVERAGE([Sales_B]))
Thank Greg. It works perfectly.
it seems that summarize would not work if I have more than 2 products
Sure it would, you just keep adding columns, you can add as many columns to summarize as you wish.
Yes, you are right. it works with many column. Thank you very much.
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |