uziel9999
2014-03-27, 08:42 PM
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?
=CALCULATE(
SUM([Commission]),
FILTER(
tCommPerMonth,
tCommPerMonth[Comp#] = RELATED(Data[Comp#])
&& tCommPerMonth[Date] = [Date]
&& [WD Balance] <> 0
&& ISBLANK([WD Balance]) = FALSE()
)
)
2176
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!
=CALCULATE(
SUM([Commission]),
FILTER(
tCommPerMonth,
tCommPerMonth[Comp#] = RELATED(Data[Comp#])
&& tCommPerMonth[Date] = [Date]
&& [WD Balance] <> 0
&& ISBLANK([WD Balance]) = FALSE()
)
)
2176
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!