Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Many appreciate for your help!
Thank you so much!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |