Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
2 – Total By Question – How can I get the % based on the score per question:
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
77 | |
76 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
65 |