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!