I have a table with the following criteria.There are multiple week endings for multiple projects. The hour/cost column can be positive or negative and are correlated (positive hours/positive costs). I'm able to get a distinct count of week ending for the project if there are any costs to it(+or-), but I want to get a distinct count only if the sum of hours or costs are positive.
Since charges can be + or -, and potentially cancel out for a week ending, this would alter my average formula if it were to count a week with sum of 0.
I'm trying to build a calculated field that I could add to my Pivot table that lists the actual charged weeks when I filter by Project. In the sample, there are 4 unique dates with sum greater than 0, but my current formula gives me 7 unique dates, disregarding positive sum.
WeekEndDate| Project | Hours | Cost
+---------------+---------+--------+-------------+
| WeekEndDate | Project | Hours | Cost |
+---------------+---------+--------+-------------+
| 10/7/16 | C7119A | 2.00 | $122.00 |
| 10/7/16 | C7119A | 32.00 | $1,952.00 |
| 10/7/16 | C7119A | 1.50 | $91.50 |
| 10/7/16 | C7119A | -32.00 | ($1,952.00) |
| 10/14/16 | C7119A | 10.00 | $610.00 |
| 10/14/16 | C7119A | -10.00 | ($610.00) |
| 10/21/16 | C7119A | 19.50 | $1,189.50 |
| 10/21/16 | C7119A | -19.50 | ($1,189.50) |
| 10/28/16 | C7119A | 2.00 | $122.00 |
| 10/28/16 | C7119A | 3.00 | $183.00 |
| 10/28/16 | C7119A | -3.00 | ($183.00) |
| 10/28/16 | C7119A | -2.00 | ($122.00) |
| 11/4/16 | C7119A | 1.00 | $61.00 |
| 11/11/16 | C7119A | 3.50 | $213.50 |
| 1/13/17 | C7119A | 3.00 | $183.00 |
+---------------+---------+--------+-------------+