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

Sum Columns Based on Values in Other Columns

Hi folks,

 

I want to add a calculated column, which sums up columns based on the value in adjacent columns. They are different types of leave over a fortnightly period. So for example if column D01Leave Type or D02Leave Type or D03Leave Type contains the text USL” I want to sum the appropriate number of hours from the D01Leave Hours and D02Leave Hours and D03Leave Hours columns. Which would result in the output below. Perhaps this would be better dealt with by a measure and I am open to this. Any help is appreciated. Thanks very much.

D01Leave Hours

D01Leave Type

D02Leave Hours

D02Leave Type

D03Leave Hours

D03Leave Type

Calculated Column total USL

7

USL

3.5

TIL

7

USL

14

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

7.8

USL

7.8

USL

15.6

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

0

NNN

0

0

NNN

0

NNN

7.5

PXD

0

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Insert a custom column with following formula

= Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUQoN9gGSxnqmQDLEE8SGicbqRCsZANl+fn5AEpM1uOTN9SzgvkGwB8qN5uDwDIhwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"D01Leave Hours" = _t, #"D01Leave Type" = _t, #"D02Leave Hours" = _t, #"D02Leave Type" = _t, #"D03Leave Hours" = _t, #"D03Leave Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D01Leave Hours", Int64.Type}, {"D01Leave Type", type text}, {"D02Leave Hours", type number}, {"D02Leave Type", type text}, {"D03Leave Hours", type number}, {"D03Leave Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours], type number)
in
    #"Added Custom"

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

Insert a custom column with following formula

= Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlfSUQoN9gGSxnqmQDLEE8SGicbqRCsZANl+fn5AEpM1uOTN9SzgvkGwB8qN5uDwDIhwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"D01Leave Hours" = _t, #"D01Leave Type" = _t, #"D02Leave Hours" = _t, #"D02Leave Type" = _t, #"D03Leave Hours" = _t, #"D03Leave Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D01Leave Hours", Int64.Type}, {"D01Leave Type", type text}, {"D02Leave Hours", type number}, {"D02Leave Type", type text}, {"D03Leave Hours", type number}, {"D03Leave Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each Number.From([D01Leave Type]="USL")*[D01Leave Hours]+Number.From([D02Leave Type]="USL")*[D02Leave Hours]+Number.From([D03Leave Type]="USL")*[D03Leave Hours], type number)
in
    #"Added Custom"

Hi Vijay_A_Verma,

 

Thank you so much for this it has really helped me! Am I correct in thinking that Number.From returns a logical 1 for true 0 for false. The documentation on the function is not very clear. Thanks so much.

Yes, it will return 1 and 0 for true and false.

Thanks again!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors