Customer Satisfaction Analysis

[Alf_C]

Hi there,

Thank you for taking interest in my subject.

I have been struggling with this topic for weeks and I know there's a solution out there just to purchase "Dynamics 365" and download the "Voice of the Customer Analytics " app but I'd like to explore more on my own.

I have two measurements for customer satisfaction.

One is CSAT score where 1 = Very Dissatisfied to  5 = Very Satisfied.

The other is NPS score where 0 (You will definitely not recommend this brand) to 10 (You will definitely recommend this brand)

The calculation is (CSAT scores* count of surveys) / count of the number of surveys.

I have two columns for each survey (1 header for each questionaire).

And I have the results in rows below with another column giving me the region of where the survey came from and which contact center service the customer.

I've tried below formulas but it always giving me error:

CSAT Score =

SUMX('Table 1','Table 1'[Count of CSAT score]/'Table 1'[Survey Measure])

Count of CSAT score =

CALCULATE('Table 1','Table 1'[CSAT score column] <> "Null" )* ('Table 1'[Survey Measure])

Survey Measure =

CALCULATE (
COUNT ( 'Table 1'[CSAT score column] ),
NOT 'Table 1'[CSAT score column] IN { "Null", " " }
)

I would appreciate any help or support to resolve this query which will give me an individual CSAT and NPS score per region or contact center for each of the two surveys.

Much appreciated

[v-frfei-msft]

Hi @Alf_C ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Alf_C]

Hi @v-frfei-msft ,

Kindly find below link:

https://1drv.ms/x/s!Aq_wrQrmKgkyxyjlgZRNeOsc4sWw?e=a5fVjJ

You can find expected result in sheet "Expected result"

Thank you 

[v-frfei-msft · Accepted solution]

Hi @Alf_C ,

I cannot work the excat same result as you need. Please check the following steps to work around.

1 Here I create measure as below to work on it.

count = COUNT('Table'[CSAT Score])

Measure = MAX('Table'[CSAT Score]) * [count]

Measure 2 = IF(ISFILTERED('Table'[CSAT Score]),BLANK(),DIVIDE(SUMX(VALUES('Table'[CSAT Score]),[Measure]),SUMX(VALUES('Table'[CSAT Score]),[count])))

8%>VOC≤2 = CALCULATE([count],FILTER('Table','Table'[CSAT Score]<=2 && 'Table'[CSAT Score]>=0)) / CALCULATE([count],ALLEXCEPT('Table','Table'[Contact Center]))

85%>VOC≥4 = CALCULATE([count],FILTER('Table','Table'[CSAT Score]<=5 && 'Table'[CSAT Score]>=3)) / CALCULATE([count],ALLEXCEPT('Table','Table'[Contact Center]))

[Alf_C]

You are amazing! Thanks a lot, Frank