# Using "SUMIF" in Calculated Column



## jack10063 (Mar 25, 2013)

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:


code
nominal
zeroPosition
ABC
100
TRUE
ABC
-100
TRUE
XZY
200
FALSE
XZY
-100
FALSE


<TBODY>

</TBODY>

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 (Mar 25, 2013)

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 (Mar 25, 2013)

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

br,
Jaakko


----------

