cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
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
4 REPLIES 4
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
Frequent Visitor

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.

Super User

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

Frequent Visitor

Thanks again!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors