Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
Please check with the simple logic like Sum(if values less than zero)
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
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
Thanks for the prompt reply Owen! I used the DAX calculated columns and this is what I was looking for.
Dennis
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |