cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VictorR Frequent Visitor
Frequent Visitor

How to create a new column that is a sum of one column and grouped by another column

Hi,

 

So I have data in a table I labelled VesselLoadDischarge that breaks down the number of containers by Discharge and Load per Vessel as shown in the image below:

 

2018-06-22_10-13-50.jpg

 

 

 

 

 

 

 

 

 I am trying to add a new column (say called SummedUnitCount) that sums the "UnitCount" column by VesselVisitCode column; ie: the first two rows would each have 642 (the sum of each row from the UnitCount column with VesselVisitCode = SCB3129 which is 377 +265), the next two rows would have 533 each (the sum of each row from the UnitCount column with VesselVisitCode = ALO3372 which is 304 + 229) and so on.

 

Note: It is not guaranteed that every entry in VesselVisitCode occurs twice, however, it is guaranteed that there can't be more than two entries per VesselVisitCode

 

Appreciate your assistance.

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: How to create a new column that is a sum of a column and grouped by another

HI @VictorR

 

This calculated column gets pretty close.  I have attached a pbix file

 

SummedUnitCount = 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[VesselVisitCode] = EARLIER('Table1'[VesselVisitCode])
            ),
      'Table1'[UnitCount])

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

2 REPLIES 2
Phil_Seamark Super Contributor
Super Contributor

Re: How to create a new column that is a sum of a column and grouped by another

HI @VictorR

 

This calculated column gets pretty close.  I have attached a pbix file

 

SummedUnitCount = 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[VesselVisitCode] = EARLIER('Table1'[VesselVisitCode])
            ),
      'Table1'[UnitCount])

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
VictorR Frequent Visitor
Frequent Visitor

Re: How to create a new column that is a sum of a column and grouped by another

Thank you Phil! That worked exactly as I was hoping and expecting it to.

 

I appreciate your time to assist me here and thank you for the sample .pbix file as well.