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
roelf
Helper II
Helper II

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
affan
Solution Sage
Solution Sage

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

Ashish_Mathur
Super User
Super User

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

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

v-frfei-msft
Community Support
Community Support

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
v-frfei-msft
Community Support
Community Support

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.
Ashish_Mathur
Super User
Super User

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/

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.

Thanks all for the assistance !

 

Regards

Roelf

affan
Solution Sage
Solution Sage

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

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.