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.

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

instead of using COUNTROWS (that counts the rows of table) you can use COUNTA, where you have to reference to a column. This function counts all values that are not empty.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy