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
Highlighted
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

Super User IV
Super User IV

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 others 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 others find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
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

Super User IV
Super User IV

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 others 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 others 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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors