SUM Based on values in another column

[roelf]

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

[affan · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

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.

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

Hi @roelf,

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

Regards,
Frank

[Ashish_Mathur · Accepted solution]

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.

[v-frfei-msft · Accepted solution]

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

[roelf]

Thanks all for the assistance !

Regards

Roelf

[affan · Accepted solution]

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