Distinct Count of weeks where sum is greater than 0 PowerPivot

Question

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

Sample Data

+---------------+---------+--------+-------------+
| 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     |
+---------------+---------+--------+-------------+

mmarie mmarie · Accepted Answer · 2017-05-05T01:31:08

You can do this using SUMMARIZE().

First create a calculated column to get a distinct ID on the project/week combo:

ProjectWeek =CONCATENATE([Project],[Week End Date])

Then create a calculated measure to count the weeks with positive sum of hours:

Positive Project Weeks:= COUNTROWS(Filter(ADDCOLUMNS(
SUMMARIZE('Project Costs','Project Costs'[Week End Date]), 
"WeekSum", MAXX(Values('Project Costs'[ProjectWeek]), 
    CALCULATE(Sum([Hours]),ALL('Project Costs'[Hours])))), 
[WeekSum] >0))

SUMMARIZE creates a table that contains the distinct WeekEndDate values. ADDCOLUMNS creates a column in that table that shows the sum of hours for that WeekEndDate. Filter removes the rows in our virtual table that have a sum of hours less than or equal to 0. Countrows then counts the rows left in the table, which is equal to the distinct count of end dates.

If you don't create the calculated column for ProjectWeek and instead use WeekEndDate in the MAXX(VALUES()) functions, you will get a total of 7 instead of 4.

Distinct Count of weeks where sum is greater than 0 PowerPivot

1 Answers