Adding a Conditional Column - add AND subtract a value to others based on criteria?

[Anonymous]

Very new to Power BI and have searched the forums on this topic, but couldn't find a solution, so thank you for your patience.

I have a simple data source that provides the below three Elements and their values (see 'Before' section). I essentially want to create a table in PBI using this data, but aggregates the three Elements down to two Elements (see 'After' section) . . . where the third or remaining Element is either added to or subtracted from the other Elements, depending on their Element "name". 

In the example above there is a column called Element that has three line items.  I essentially want to aggregate these three Element column values into two line items where . . . 

IT = (07. IT)  +  (02. Indirect - Tower)  =  ($300)  +  ($100) = $400

Procurement = (03. Procurement (rate))  -  (02. Indirect - Tower)  =  ($150)  -  ($100) = $50

Note: 02. Indirect - Tower gets added to 07. IT, but subtracted from 03. Procurement (rate) when creating the new Conditional Columns.  Is this possible to do?

I used the Power Query Editor, Add Conditional Column option to create a new column name, in this case "cust_Element" and establishing the relationship of the original three Elements (see below), but not sure if this is where one would indicate that '02. Indirect - Tower' needs to subtracted from '03. Procurement (rate)'?  The resulting table didn't look to reflect the '02. Indirect - Tower' and '03. Procurement (rate)' relationship and I'm thinking that's because '02. Indirect - Tower' already has a relationship established with '01. IT'?

A somewhat simple request, but difficult to explain.  Hopefully that makes sense?  Just wondering if this can be accomplished and if so, how?  Thank you so much!

[parry2k · Accepted solution]

@Anonymous it is a good candidate for a DAX measure instead of doing this in PQ and preparing the data upfront:

Add these measures:

Base Sum = SUM ( Table[Value] )

Indirect - Tower = CALCULATE ( [Base Sum], Table[Element] = "02. Indirect - Tower" )

IT = CALCULATE ( [Base Sum], Table[Element] = "01.IT"  ) + [Indirect - Tower]

Procurement = 
CALCULATE ( [Base Sum], Table[Element] = "03. Procurement (Rate)" ) - [Indirect - Tower]

Now you can use IT and Procecuement measures in any visual, also it will work if you slice and dice the data by any columns in your model.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexisOlson]

I would try creating two conditional columns, IT and Procurement, and then unpivot and group like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI2MFCK1YlWMgKyDaFsYxDbFMiOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Element = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Element", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IT", each if [Element] = 1 then [Value] else if [Element] = 2 then [Value] else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Procurement", each if [Element] = 3 then [Value] else if [Element] = 2 then -[Value] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Element", "Value"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Cust_Element", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Cust_Element"}, {{"Value", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

[parry2k]

@Anonymous are these the only conditions/business logic or there are elements and have different logic? That kind of detail will help to determine the solution.

[Anonymous]

Hi parry2k,

Thank you for your inquiry.  To answer your question, those are the only conditions.

• 02. Indirect - Tower is be added to 01. IT to create a new column Element called "IT"
• 02. Indirect - Tower is be subtracted from 03. Procurement (rate) to create a new column Element called "Procurement"

Thank you for your help!