Sum Columns Based on Values in Other Columns

[eager_to_learn]

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

[Vijay_A_Verma · Accepted solution]

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"

[eager_to_learn]

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.

[Vijay_A_Verma]

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

[eager_to_learn]

Thanks again!