How can I add a column to calculate average CSAT score in EazyBI

[Veronica Qu]

Hi experts,

I want to add a column to calculate the average CSAT score in EazyBI, for example in the below screenshot, I want to add a column after "5" and calculate the average CST score for the team "Global Learning Experience" and it should be "(4*1 + 5*17)/(1+17)=4.9. How can I achieve this? Any automatic measure I can use to calculate the average score or I have to set up a new measure by myself. If so, what should be the measure like? Thank you!  

[elita_kalane]

Hi @Veronica Qu 

Thanks for posting your question! 

I suggest defining a new calculated measure (https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures) with the formula below, wherever you see "Issue components" (the bolded lines) in my formula below, please replace it with the name of your field (make sure the field is imported as a property). I don't see the precise name of your dimension used, but I assume it would be "Issue CSAT score". Before saving the formula, choose formatting - numeric -> decimal

AVG(
 Filter(
   Descendants([Issue].CurrentMember,[Issue].[Issue]),
  Val(ExtractString([Measures].[Issue components], "(\d+)\.\d+$", 1)) > 0
 ),
[Measures].[Issues created]
*
Val(ExtractString([Measures].[Issue components], "(\d+)\.\d+$", 1))
)

 The result will be similar as in the screenshot below:

Best wishes,

Elita from support@eazybi.com

[Veronica Qu]

Hey Elita, thank you so much for your detailed reply! I have tried this formula however it doesn't work and the error shows as the below screenshot:

And the formula I used is as below:

Do you have any idea where's the problem coming from? Thank you so much for your support!

[elita_kalane]

Hi @Veronica Qu

Thanks for the follow-up and the screenshot!

I assumed the field was a string field, but it seems it is a decimal field, my apologies. 

Please try the formula below instead. For the bolded parts, I suggest using the autofill functionality. 

AVG(
  Filter(
   Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[CSAT Score created] > 0
  ),
[Measures].[Issues created]
*
[Measures].[CSAT Score created]
)

 Best wishes,

Elita from support@eazybi.com

[Veronica Qu]

Hey Elita,

Thank you so much for your response and awesome support! I'm sorry that I may need your further support on it. I've tried the new formula you shared, and the outcome is a bit different from what I would want to achieve. As shown in the below screenshot, the average CSAT score for all assignees should be calculated as "(1*4 + 20*5)/(1+20) =4.95. May I seek your support on how to modify the formula accordingly? 

Thank you so much!

[Danut M _StonikByte_]

Hi @Veronica Qu,

Perhaps is better to contact their support team.

In case you have our Great Gadgets also installed, take a look over this article:

https://community.atlassian.com/t5/App-Central-articles/How-to-measure-Customer-Satisfaction-in-Jira-and-Confluence-with/ba-p/2741012

Danut.

[Veronica Qu]

Thank you so much Danut! I will look over this article for solutions. Thanks for sharing!

[Fabian Lim]

Hi @Veronica Qu

I suggest you look at the dashboard example here.  https://eazybi.com/accounts/1001/dashboards/8347-sla-overview

Instead of having the components you may want to have the assignees dimension.  

Regards,

Fabian

[Veronica Qu]

Thank you so much Fabian, this sample dashboard is really useful to refer! Thanks for sharing!