cancel
Showing results for
Did you mean:
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
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]))`

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

Regards,

Affan

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

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.
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.
5 REPLIES 5
Highlighted
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]))`

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

Regards,

Affan

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

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.
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.
Regular Visitor

## Re: SUM Based on values in another column

Thanks all for the assistance !

Regards

Roelf

Announcements

#### 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?

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

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