Forum Discussion
Filtering a Sum
I am having trouble describing this, so it's possible Filtering a Sum is the wrong subject line.
I have a table with some distance measurements and a flag dictating whether that measurement passes a threshold. The measurements normally come in every minute, however we have chosen to measure these as a SUM every five minutes. They are linked to a Date table that is set to 5 minute increments (only 14 days worth, so not too huge).
| Date/Time | Distance | Pass |
| 30/06/2026 04:55:00 | 100 | False |
| 30/06/2026 05:00:00 | 200 | True |
| 30/06/2026 05:00:00 | 150 | False |
| 30/06/2026 05:05:00 | 200 | True |
As you can see, the total SUM of all columns is 650. When group by date, the sum 100, 350, 200 respectively. The issue I am seeing and can't resolve is that I would like measures of Total Distance Passed and Total Distance Failed that I can group by date
I am having trouble with the fact that I might get three or more entries rounded to the same date, in which some may pass and some may fail. What this means is that some rows are included in both measures. In other worlds, for 5pm, I get the result is that the distance both passed and failed at the same time. I need a way to be able to look at the entries in the group, and say that if there are more or equal passed entries, treat that time as passed, other wise treat it as failed. And then be able to sum all the entries in that time to be my total distance for that time.
Iope this makes sense. Can change the subject if I need better wording.
For this you can use two measures that check inside the current filter whether the bucket has more or equal passed rows, and if so put the full bucket sum under Passed, otherwise put it under Failed.
Total Distance Passed =
VAR PassCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = TRUE())
VAR FailCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = FALSE())
VAR BucketSum = SUM('YourTable'[Distance])
RETURN IF(PassCount >= FailCount, BucketSum, BLANK())Total Distance Failed =
VAR PassCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = TRUE())
VAR FailCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = FALSE())
VAR BucketSum = SUM('YourTable'[Distance])
RETURN IF(PassCount >= FailCount, BLANK(), BucketSum)Drop both into a visual grouped by the Date/Time column from your Date table. On your sample rows you should see 100 under Failed at 04:55, 350 under Passed at 05:00 (the tie goes to Passed because we use >=), and 200 under Passed at 05:05. If your Pass column is stored as text rather than boolean, swap TRUE() and FALSE() for "True" and "False".
If this helped, please mark it as the accepted solution and give it a thumbs up.
Thanks,
Shai Karmani
3 Replies
For this you can use two measures that check inside the current filter whether the bucket has more or equal passed rows, and if so put the full bucket sum under Passed, otherwise put it under Failed.
Total Distance Passed =
VAR PassCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = TRUE())
VAR FailCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = FALSE())
VAR BucketSum = SUM('YourTable'[Distance])
RETURN IF(PassCount >= FailCount, BucketSum, BLANK())Total Distance Failed =
VAR PassCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = TRUE())
VAR FailCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[Pass] = FALSE())
VAR BucketSum = SUM('YourTable'[Distance])
RETURN IF(PassCount >= FailCount, BLANK(), BucketSum)Drop both into a visual grouped by the Date/Time column from your Date table. On your sample rows you should see 100 under Failed at 04:55, 350 under Passed at 05:00 (the tie goes to Passed because we use >=), and 200 under Passed at 05:05. If your Pass column is stored as text rather than boolean, swap TRUE() and FALSE() for "True" and "False".
If this helped, please mark it as the accepted solution and give it a thumbs up.
Thanks,
Shai KarmaniThis seems to work, except I am then having trouble with summarising the ungrouped values. In the screen shot below that individual rows for each date are correct, but the SUM of passed and failed distances is wrong. What would be the cause of this?
Total Distance Passed =
SUMX(
VALUES('Date'[Date/Time]),
VAR _pass = CALCULATE(COUNTROWS('Table'), 'Table'[Pass] = TRUE())
VAR _fail = CALCULATE(COUNTROWS('Table'), 'Table'[Pass] = FALSE())
RETURN IF(_pass >= _fail, CALCULATE(SUM('Table'[Distance])), 0)
)
