cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

Summarize table based on two coulmns and sum sales

Hi

 

Let's say I have this table:

 

ProductMonthSales
P1Jan-20100
P1Jan-20200
P2Jan-20500
P2Feb-20600
P1Mar-2050
P2Mar-20300

 

 

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

 

ProductMonthSales
P1Jan-20300
P1Mar-2050
P2Jan-20500
P2Feb-20600
P2Mar-20300

 

Your help will be much appreciated.

 

Thanks.

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Greg_Deckler
Super User
Super User

@mb0307 -

Perhaps:

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@mb0307 -

Perhaps:

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

amitchandak
Super User
Super User

@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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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  

@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]))

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler 

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

 

Thanks for the reply.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!