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#] filter- $1,212,904 is the sum for everything; the formula should have given $75,598. Would someone please help by providing the correct syntax?
Expected Output
Thank you in advance!
Code:
=CALCULATE(
SUM([Commission]),
FILTER(
tCommPerMonth,
tCommPerMonth[Comp#] = RELATED(Data[Comp#])
&& tCommPerMonth[Date] = [Date]
&& [WD Balance] <> 0
&& ISBLANK([WD Balance]) = FALSE()
)
)
Expected Output
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 |
Thank you in advance!