# DAX Sum based on Filter for every row

• ### Question

• Hello, I am trying to sum a column based on certain filters for every row in the table. The formula gives the total, but ignores the [Comp#] and [Date] filters- \$

• 1,212,904 is the sum for all Companies and all Dates where [WD Balance]<>0
• The formula should have given \$0 for both 8/31/2013 Dates
• The formula should have given \$4,348 for both 9/30/2013 Dates

Code:
```=CALCULATE(
SUM([Commission]),
FILTER(
tCommPerMonth,
tCommPerMonth[Comp#]  =  RELATED(Data[Comp#])
&& tCommPerMonth[Date]   =  [Date]
&& [WD Balance]          <> 0
&& ISBLANK([WD Balance]) =  FALSE()
)
)```

The picture below is directly from PowerPivot.

Expected Output: (Commissions for CO1022 where [WD Balance]<>0 by date)

 Date Commission 9/30/2013 \$4,348 10/31/2013 \$2,860 11/30/2013 \$2,768 12/31/2013 \$5,819 1/31/2014 \$2,138 2/28/2014 \$1,784 3/31/2014 \$13,920 4/30/2014 \$1,748 5/31/2014 \$1,314 6/30/2014 \$12,836 7/31/2014 \$650 8/31/2014 \$626 9/30/2014 \$10,883 10/31/2014 \$134 11/30/2014 \$129 12/31/2014 \$9,267 1/31/2015 \$134 2/28/2015 \$121 3/31/2015 \$765 4/30/2015 \$129 5/31/2015 \$134 6/30/2015 \$741 7/31/2015 \$134 8/31/2015 \$134 9/30/2015 \$707 10/31/2015 \$134 11/30/2015 \$129 12/31/2015 \$722 1/31/2016 \$134 2/29/2016 \$125 3/31/2016 \$129 Total \$75,598

Thursday, March 27, 2014 7:20 PM

• I'm a little confused about the shape of your model and names of your tables but if say your sample table from PowerPivot is called 'Table1' then the following should get you what you want.

As a Calculated Column (like your Test column above), this should work:

```=IF(
Table1[WD Balance] <> 0
&& NOT(ISBLANK(Table1[WD Balance])),
CALCULATE(
SUM([Commission]),
ALLEXCEPT(
Table1,
Table1[Date],
Table1[Comp#]
)
),
BLANK()
)```

If you wanted such a calculation to work as a Measure, you would have to do it a little differently:

```Measure Version:=
SUMX(
FILTER(
ALLEXCEPT(
Table1,
Table1[Comp#],
Table1[Date]
),
Table1[WD Balance] <>0
&& NOT(ISBLANK(Table1[WD Balance]))
),
CALCULATE(
SUM(Table1[Commission])
)
)```

Thursday, March 27, 2014 8:32 PM