Novice; Calculating CSAT (data presented differently)

[webbj]

I am wondering if anyone can help - I'm still new to powerbi and data analysis as a whole. Here's a bit of information regarding the issue, question at the end! I've previously calculated CSAT in powerbi however the data layout was completely different so I've been thrown off!

So this is how the data is being presented, it initially comes as a json file so the manipulation is to get it into a readable state. I've added two extra columns

1 = GoodRatings/TotalRatings, which contains the results of GoodRatings divided by Total 

2 = CSAT column which is essentially a duplicate of the other one but displayed as percentage

I haven't weighted the ratings i.e. good = 3, average = 2, poor = 1 (though would be nice to)

Data import layout

Data

Table created in powerbi report section (although exported to excel and org names replaced)

So - how can I merge all the good, average, poor & total ratings etc per org to be able to calculate each organisations CSAT score?

i.e ForEach Org Sum of GoodRaings / Sum of Total Ratings * 100

&

How best to weight the responses given how the data is currently presented? (I've seen methods of calculating CSAT with and without weighting the responses, is it worth doing?)

[v-henryk-mstf]

Hi @webbj ,

What do you mean by trade-off response and to what degree of optimization? Is it possible to explain it to facilitate my understanding of your needs? Looking forward to your reply.

Best Regards,
Henry

[webbj]

Morning Henry, 

Apologies I haven't explained my issue very well, probably because I dont understand exactly the issue is to be resolved. 

Using the data from the first image above I would like to create a CSAT graph to show an overall for each organisation (so orgs are shown in order of their CSAT score), then be able to filter it by channel i.e. only use web channel or only use email or use web and email but exclude others. 

I hope this makes more sense. Thankyou for your time.

Kind regards, 

Jack