locked
Counting non-blank rows when calculating a cumulative number RRS feed

  • Question

  • Hi

    I have tried the below formula and derivations of it (using counta, countax) in trying to create a cumulative number that does not count blank cells/records.

    Every variation I try however still counts blank cells/records. Does anyone have any ideas?

    =CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

    Paul

    Monday, March 9, 2015 6:38 PM

Answers

  • Hi Paul,

    use "&&" to add filter arguments:

    =CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory]) &&  NOT(ISBLANK([entity_id])),s9JudgeFTC[reentry]=1)


    Imke

    • Marked as answer by Paul-NYS Wednesday, March 11, 2015 1:25 PM
    Wednesday, March 11, 2015 1:15 PM
    Answerer
  • Hi Imke

    Actually, a slight variation of your formula above works:

    =CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),NOT(ISBLANK(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

    Thanks a lot!!

    Paul

    • Marked as answer by Charlie Liao Thursday, March 12, 2015 8:13 AM
    Wednesday, March 11, 2015 1:24 PM

All replies

  • Hi Paul,

    try adding this additional filter argument: NOT(ISBLANK([entity_id])



    Imke

    Tuesday, March 10, 2015 8:35 PM
    Answerer
  • Hi Imke

    That make work in theory (and I will use it going forward for other items), but the Filter function can only take two arguments apparently and it is erroring on that.

    Paul

    Wednesday, March 11, 2015 1:10 PM
  • Hi Paul,

    use "&&" to add filter arguments:

    =CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory]) &&  NOT(ISBLANK([entity_id])),s9JudgeFTC[reentry]=1)


    Imke

    • Marked as answer by Paul-NYS Wednesday, March 11, 2015 1:25 PM
    Wednesday, March 11, 2015 1:15 PM
    Answerer
  • Hi Imke

    Actually, a slight variation of your formula above works:

    =CALCULATE(count(s9JudgeFTC[entity_id]),FILTER(ALL(s9JudgeFTC[ExitMonthCategory]),s9JudgeFTC[ExitMonthCategory] <= MAX(s9JudgeFTC[ExitMonthCategory])),NOT(ISBLANK(s9JudgeFTC[ExitMonthCategory])),s9JudgeFTC[reentry]=1)

    Thanks a lot!!

    Paul

    • Marked as answer by Charlie Liao Thursday, March 12, 2015 8:13 AM
    Wednesday, March 11, 2015 1:24 PM