Summarize table based on two coulmns and sum sales

[mb0307]

Hi

Let's say I have this table:

Product	Month	Sales
P1	Jan-20	100
P1	Jan-20	200
P2	Jan-20	500
P2	Feb-20	600
P1	Mar-20	50
P2	Mar-20	300

I want to create a NEW TABLE with summarized result like this:

Product	Month	Sales
P1	Jan-20	300
P1	Mar-20	50
P2	Jan-20	500
P2	Feb-20	600
P2	Mar-20	300

Your help will be much appreciated.

Thanks.

[amitchandak · Accepted solution]

@mb0307 ,  not sure on the need for a new table.

New table =summarize(Table, Table[Product],Table[Month],"Sales" ,sum(table[Sales]))

You can use measure sum(table[Sales]) on any visual with Product and Month  and you will get this (Table or Matrix )

[Greg_Deckler · Accepted solution]

@mb0307 -

Perhaps:

New Table = SUMMARIZE('Table',[Product],[Month],"Sales",SUM('Table'[Sales]))

[amitchandak · Accepted solution]

@mb0307 ,  not sure on the need for a new table.

New table =summarize(Table, Table[Product],Table[Month],"Sales" ,sum(table[Sales]))

You can use measure sum(table[Sales]) on any visual with Product and Month  and you will get this (Table or Matrix )

[mb0307]

@amitchandak  Thanks and this works perfectly.  

Is it possible to sum the SALEAS based on a filter within same DAX please? 

For example:  If i have a measure which is calculating a Max Week for each month (Jan max week is WK5, Feb is WK9),  Can I use that measure to filter the new table?   So Jan-20 ignore WK1,2,3,4 and only sum values by WK5 values?

Thanks  

[Greg_Deckler]

@mb0307 - Sounds like you would need something like:

New Table = SUMMARIZE(FILTER('Table','Table'[Week] = [Max Week Measure]),'Table'[Product],'Table'[Month],"Sales",SUM('Table'[Sales]))

[mb0307]

@Greg_Deckler 

I will post my query in a new post with an example.

Thanks for the reply.