Forum Discussion
Issue with SUMX including null values in formula
I have the following dataset that includes a date, a store ID, and a profit value for that month. One site has not compiled it's report yet for the time frame:
| Date | ID | Profit |
| 10/1/2019 | 1 | -25 |
| 10/1/2019 | 2 | 24 |
| 10/1/2019 | 3 | 0 |
| 10/1/2019 | 4 | |
| 10/1/2019 | 5 | -1 |
| 11/1/2019 | 1 | 34 |
| 11/1/2019 | 2 | 14 |
| 11/1/2019 | 3 | 3 |
| 11/1/2019 | 4 | |
| 11/1/2019 | 5 | -5 |
My goal is to countthe number of sites in a given time period that are profitable. Profitable in this case is anything that is greater than or equal to 0. My current measure for this is as follows:
Positive Values = SUMX(
VALUES(Table[ID]),
IF(CALCULATE(SUM(Table[Profit])) >= 0, 1 , BLANK()))
Here's the expected values I should get with the measure given each time frame on a Date Slicer:
10/1/2019: 2
10/1/2019: 2
11/1/2019: 3
10/1/2019 - 11/1/2019: 3
However, the SUM formula is including the null values, giving me values of 3, 4, and 4, respectively. I'm not sure where to put the FILTER(ISBLANK(Table[Profit]) = FALSE) conditional in the measure to account for this. Anyone have any suggestions?