Forum Discussion
Customer Satisfaction Index (CSAT) dynamic calculation
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
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])/10Result:
and here is sample pbix file, please try it.
Regards,
Lin
4 Replies
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])/10Result:
and here is sample pbix file, please try it.
Regards,
Lin
Can you post that data as text or post a link to a file with the data?
Hi, Greg!
Thanks for your time
Here you are: https://drive.google.com/open?id=1fGL54h2wl676wfwfCeXixyZuIxNo4HA1
