Using "SUMIF" in Calculated Column

[jack10063]

Hi,

I just started to explore DAX functions so I apologize that this is a newbie question. I have a data set with 2 columns: code & nominal. I'd like to add a calculated column to check if the aggregated sum of nominal per code is zero. So, I'd like to add a zeroPosition column as below:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]code
[/TD]
[TD]nominal
[/TD]
[TD]zeroPosition
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]100
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]-100
[/TD]
[TD]TRUE
[/TD]
[/TR]
[TR]
[TD]XZY
[/TD]
[TD]200
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]XZY
[/TD]
[TD]-100
[/TD]
[TD]FALSE
[/TD]
[/TR]
</TBODY>[/TABLE]


In Excel the formula would be: =IF(SUMIF($A$2:$A$5;A2;$B$2:$B$5)=0;TRUE;FALSE). But how should I do this with DAX?

br,
Jack

 

[Jacob Barnett]

Jack, the answer to you question (assuming your table is called table1 and I used thecode instead of code as the column heading) is:

=if(CALCULATE(
               SUM(table1[nominal]),
               ALL(Table1),
               Table1[thecode]=EARLIER(table1[thecode])
                     )=0,
                 "True", "False")

Calculated columns are usually the easy way in for the Excel guys like us but the real power is when you start to use DAX in measures. CALCULATE(), FILTER() and ALL() are absolutely key and I would recommend reading:

SQLBI - Marco Russo : How CALCULATE works in DAX

ALL() Revisited « PowerPivotPro

FILTER() – When, Why, & How to Use It « PowerPivotPro

 

[jack10063]

Thanks a lot Jacob, your example works nicely! And thanks for the references I have to start studying this mysterious DAX language!

br,
Jaakko