Forum Discussion

JasonBurdetts's avatar
JasonBurdetts
Frequent Visitor
1 month ago
Solved

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 passe...
  • Shai_Karmani's avatar
    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