Novice; API RAW data manipulation to calculate CSAT

[webbj]

Hello All, I'm a complete novice with PowerBI and data analysis in general.. so any help would be much appreciated. 

I am trying to calculate average CSAT inside Powerbi. The RAW data is being pulled in via an API. 

There's no 'score' associated with the 'Rating' so I created a table in the Model section called CSAT Response Weight with two columns called weight.id and weight.score and linked the tables together via weight id and availablecategories.name.

I was then trying to multiply the score and the count of categories but I've gone wrong somewhere as it's not multiplying the numbers i question i.e. excellent = 5, score= 4 so it should be 5 * 4 = 20

Count of results.categories x Count of weight.score = COUNTA('CSAT Results'[results.categories]) * COUNTA('CSAT Response Weight'[weight.score])

However I at a loss of what to do next / if what I have done is the best way to go about this / if this is correct! 

Any help would be much appreciated, if more information is required please let me know.

[amitchandak · Accepted solution]

@ , create a measure like

Count of results.categories x Count of weight.score =
sumx('CSAT Categories[Avaiable category Name]', calculate( COUNTA('CSAT Results'[results.categories]) * MAx('CSAT Response Weight'[weight.score])))

[webbj]

Sorry for the delay! - this really helped thankyou