cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roelf Regular Visitor
Regular Visitor

SUM Based on values in another column

Hi

 

Probably a very basic question, but I"m stuck.

 

My table looks like this

 

Customer  Week Sale

A                  1       100

B                  1        200

A                  1         50

B                  1         10

A                  2        50

B                  2        150

A                  2         50

 

I want to be able to SUM sales per customer, per week

 

 

I want my table to look like this afterwards

Cust  Week   Sale Total

A        1         100  150

B        1         200  210

A        1         50   150

B        1         10    210

 

I would expect to use a SUMX, but I have not been able to understand how that works... Any help will be appreciated.

 

Thanks

R

4 ACCEPTED SOLUTIONS

Accepted Solutions
affan Established Member
Established Member

Re: SUM Based on values in another column

Hi @roelf,

 

A simple SUM with filter would resolve the requirement

 

Total = CALCULATE(SUM('Table'[Sale]),ALLEXCEPT('Table','Table'[Customer],'Table'[Week]))

Sum based on other column.png

 

 

 

 

Please mark this post as an accepted solution if this helped you.

 

Regards,

Affan

View solution in original post

Highlighted
Super User
Super User

Re: SUM Based on values in another column

Hi,

 

In a Matrix visual, drag all columns to the Row labels section.  Write this measure

 

=CALCULATE(SUM(Data[Sale]),ALL(Data[Week])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Community Support Team
Community Support Team

Re: SUM Based on values in another column

Hi @roelf,

 

One sample for your reference. Here we need to create a measure as below.

 

Measure = CALCULATE(SUM(Table1[Sale]),ALLEXCEPT(Table1,Table1[Customer],Table1[Week]))

Capture.PNG

 

Also please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: SUM Based on values in another column

Hi @roelf,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
affan Established Member
Established Member

Re: SUM Based on values in another column

Hi @roelf,

 

A simple SUM with filter would resolve the requirement

 

Total = CALCULATE(SUM('Table'[Sale]),ALLEXCEPT('Table','Table'[Customer],'Table'[Week]))

Sum based on other column.png

 

 

 

 

Please mark this post as an accepted solution if this helped you.

 

Regards,

Affan

View solution in original post

Highlighted
Super User
Super User

Re: SUM Based on values in another column

Hi,

 

In a Matrix visual, drag all columns to the Row labels section.  Write this measure

 

=CALCULATE(SUM(Data[Sale]),ALL(Data[Week])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Community Support Team
Community Support Team

Re: SUM Based on values in another column

Hi @roelf,

 

One sample for your reference. Here we need to create a measure as below.

 

Measure = CALCULATE(SUM(Table1[Sale]),ALLEXCEPT(Table1,Table1[Customer],Table1[Week]))

Capture.PNG

 

Also please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: SUM Based on values in another column

Hi @roelf,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

roelf Regular Visitor
Regular Visitor

Re: SUM Based on values in another column

Thanks all for the assistance !

 

Regards

Roelf

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 245 members 2,359 guests
Please welcome our newest community members: