Add a column with just the sum of negative values from other columns

[DJB]

Hi everyone,

I got quite an extensive data model in which I would like to make some calculations on row level. One of them is to separate negative (and eventually positive) values from different columns by summarzing them in two new columns.

In this example I would like the sum of the negative values in columns "delta +1", "delta +2", "delta +3" and " Delta +4". The third row should show -10.000.000

How can this be solved in an easy way?

Thanks in advance!

[zlokesh]

Please check with the simple logic like    Sum(if values less than zero)

[OwenAuger · Accepted solution]

@DJB

In the Power Query editor, you could add a Custom Column with expression in the dialog box:

List.Sum ( List.Select( { [#"delta + 1"], [#"delta + 2"], [#"delta + 3"], [#"delta + 4"] }, each _ < 0 ) )

In a DAX calculated column, you could use this formula:

Negative Sum =
VAR Numbers = { TableName[delta + 1], TableName[delta + 2], TableName[delta + 3], TableName[delta + 4] }
VAR NegativeNumbers = FILTER ( Numbers, [Value] < 0 )
RETURN
    SUMX ( NegativeNumbers, [Value] )

Regards,

Owen

[Rygaard]

Thank you !

This was on page 4 on google when googling "power query add multiple columns only if positive number" - so ill just add that phrase to it. also

"power query add multiple columns only if conditions are meet. "

perhaps this will help others find it in the future

[DJB]

Thanks for the prompt reply Owen! I used the DAX calculated columns and this is what I was looking for.

Dennis