Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Non Blank Rows w/ Filters

I am trying to calculate my NPS scores but it is counting blanks in the distractors and total rows.   Calculated Participant NPS (ICU United) = VAR _participantAdvocatesICU = (CALCULATE(COUNTROW...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    If you want to use <> blank in your formula, the measure should be like this:

    Calculated NPS =
VAR _participantAdvocates =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] >= 9
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _participantDetractors =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] <= 6
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _totalParticipantResponses =
    CALCULATE ( COUNTROWS ( 'Primary' ), 'Primary'[NPS Score] <> BLANK () )
RETURN
    DIVIDE (
        _participantAdvocates - _participantDetractors,
        _totalParticipantResponses
    )

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I tried the following but did not seem to work. 

 

Calculated NPS =
VAR _participantAdvocates = CALCULATE(COUNTROWS('Primary'), 'Primary'[NPS Score] >= 9 <>BLANK())
VAR _participantDetractors = CALCULATE(COUNTROWS('Primary'), 'Primary'[NPS Score] <= 6 <>BLANK())
VAR _totalParticipantResponses = CALCULATE(COUNTROWS('Primary') <>BLANK())
RETURN DIVIDE(_participantAdvocates - _participantDetractors, _totalParticipantResponses)

 

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi Anonymous ,

If you want to use <> blank in your formula, the measure should be like this:

Calculated NPS =
VAR _participantAdvocates =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] >= 9
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _participantDetractors =
    CALCULATE (
        COUNTROWS ( 'Primary' ),
        FILTER (
            ALL ( 'Primary' ),
            'Primary'[NPS Score] <= 6
                && 'Primary'[NPS Score] <> BLANK ()
        )
    )
VAR _totalParticipantResponses =
    CALCULATE ( COUNTROWS ( 'Primary' ), 'Primary'[NPS Score] <> BLANK () )
RETURN
    DIVIDE (
        _participantAdvocates - _participantDetractors,
        _totalParticipantResponses
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.