Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mb0307
Responsive Resident
Responsive Resident

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 )

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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 )

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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

 

Thanks for the reply.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.