Turning numbers into percent's with calculations

[RichOB]

Hi, I need help with some calculations for a survey.

In the example below the survey consists of 3 questions that are always in number form, “I Am Happy, I am Sad, I Am Neither”. The most 1 person can score per question is always 5 which means the total possible survey score is always 15. I need help with calculations to change them into percentages. The Total by City doesn't have a fixed number to calculate against (like 15 above) as it always changes due to the number of people within the city varying. 

1 – Total by City. How can I turn the numbers into % based on the amount of people who took the survey:

• For example, 5 people from Newcastle took the survey so the total possible score is 75. They scored 66….66 / 75 x 100 = Newcastle average total score is 86.7%

2 – Total By Question – How can I get the % based on the score per question:

• For example, Newcastle scored 21 out of 25 for “ I Am Happy “ = all people in Newcastle who picked I Am Happy is 84%

3 – Total By Person - For example, if Person 1 scored 13 out of 15, their score is 86.6%

I hope this makes sense, happy to give more information and thanks in advance.

Person	Completion time	I Am Happy	I Am Sad	I Am Neither	Area	City	Pet
1	03/01/2024	4	4	5	North	Newcastle	Dog
2	11/01/2024	5	5	5	North	Newcastle	Dog
3	25/01/2024	3	3	4	North	Newcastle	Dog
4	29/01/2024	4	4	5	North	Newcastle	Cat
5	01/02/2024	5	5	5	North	Newcastle	Cat

[Anonymous · Accepted solution]

Hi @RichOB ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Total By city = 
var _a=CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER(ALLSELECTED('Table'),'Table'[City]=MAX('Table'[City])))
var _b=_a*15
var _c=CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[City]=MAX('Table'[City])))
RETURN DIVIDE(_c,_b,0)

Total By Question = 
var _a=CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER(ALLSELECTED('Table'),'Table'[City]=MAX('Table'[City])))
var _b=_a*5
var _c=CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[City]=MAX('Table'[City]) && 'Table'[Attribute]=MAX('Table'[Attribute])))
RETURN DIVIDE(_c,_b,0)

Total By Person = 
var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Person]=MAX('Table'[Person])))
var _b=15
RETURN DIVIDE(_a,_b,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.