Forum Discussion

PaulCo's avatar
PaulCo
Helper II
8 years ago
Solved

Survey Response Rate

I'm pulling survey data from SFDC where a table diplays a survey score if a response has been received from a customer. 

 

I want to create a measure of the percentage of customers that have responded. Multiple surveys have been sent out to different contacts in each customer and we only need one of those responses to mark that customer as responded.

 

CustomerScore 
A7
A 
B6
B10
B9
C5
C 
D10
E 

 

In the above example we have 5 customers and 4 of them have submitted a response so the response rate would be 80%.

 

How do i calculate one response for each customer from the multiple scores?

 

Thanks

Paul

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

     

    PaulCo

     

    Hi, try with this measure

     

    Response Rate =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( Table1[Customer] ),
        FILTER ( Table1, Table1[Score ] <> BLANK () )
    ),
    DISTINCTCOUNT ( Table1[Customer] )
)

    Regards

     

    Victor

    Lima - Peru

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

     

    PaulCo

     

    Hi, try with this measure

     

    Response Rate =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( Table1[Customer] ),
        FILTER ( Table1, Table1[Score ] <> BLANK () )
    ),
    DISTINCTCOUNT ( Table1[Customer] )
)

    Regards

     

    Victor

    Lima - Peru

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    You're solely interested in whether there's been a reply or not? Try grouping the rows by customer in Power Query and summing the score column. If there's no replies, then this should sum to null, so you can get the total number of customers by countrows on your new table, and then the number of customers on that have replied by doing the same, but wrapping that in Calculate with the summed up response row not equal to blank.

     

    Not sure whether you can get a response of zero which might break this response, if so you may need to put in a helper column that looks at whether the initial score column is blank and returning null if it is blank and 1 otherwise