Forum Discussion
Filtering a Sum
- 1 month ago
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
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
This 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?