Customer Satisfaction Index (CSAT) dynamic calculation

[Anonymous]

Hi guys,

there are some issues with CSAT calculation.

Here you are the table main table in Excel:

The algorithm is a follow:

1. Average for columns Importance and Rating (column J, K). 

2. For each Importance value in row marking with attribute, calculate it IImportance weight (column F). 

Excel Formula: F2 = D2/VLOOKUP(C2;I:K;2;0)

3. Calculate Weited value for each row record (column G). 

Excel Formula: G2 = F2 * E2

4. Calculate Weighted mean (column L) for each feature group.

Excel Formula: L4 = AVERAGEIF(C:C;I4;G:G)

5. CSI = AVERAGE (L4:L6)/10

So, I have a trouble from step 2 (sad, but true).

Moreover, the calculations shoud be dynamic, i.e. the CSAT is changed if you filter it by Date or № resp.

Could you please help me with this (steps from 2 to 5)? 

Link to file:  https://drive.google.com/open?id=1fGL54h2wl676wfwfCeXixyZuIxNo4HA1

Many thanks for you kind attention

[v-lili6-msft · Accepted solution]

hi @Anonymous 

For [Importance weight] and [Weighted value], you could create two calculated columns by these formula

Importance weight = 'Table'[Importance]/CALCULATE(AVERAGE('Table'[Importance]),ALLEXCEPT('Table','Table'[Feature]))

Weighted value = 'Table'[Importance weight]*'Table'[Rating]

For [Appraisal_mean], [Importance_mean], [Weighted_mean] and [CSI], you could create Calculated measure as below:

Appraisal_mean = CALCULATE(AVERAGE('Table'[Rating]),ALLEXCEPT('Table','Table'[Feature]))

Importance_mean = CALCULATE(AVERAGE('Table'[Importance]),ALLEXCEPT('Table','Table'[Feature]))

Weighted_mean = CALCULATE(AVERAGE('Table'[Weighted value]),ALLEXCEPT('Table','Table'[Feature]))

CSI = AVERAGEX(VALUES('Table'[Feature]),[Weighted_mean])/10

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Many appreciate for  your help! 

Thank you so much!

[Greg_Deckler]

Can you post that data as text or post a link to a file with the data?

[Anonymous]

Hi, Greg! 

Thanks for your time

Here you are: https://drive.google.com/open?id=1fGL54h2wl676wfwfCeXixyZuIxNo4HA1