Forum Discussion
Use forms categories to calculate average
I'm working on a visual that should calculate the average per [certain filter].
The categories would look something like:
fully disagree, disagree, neutral, agree, fully agree
And there are multiple columns that use this system that I need to visualize.
Right now I set the text to numbers to calculate it by changing the sheet, but that is not actually what I want.
Where the numbers are, I would like to have the categories, but still calculated like this. How do I do this?
| Business unit | Department | Do you agree to X | Do you agree to Y |
| Sustainable Fashion | A | Fully disagree | Disagree |
| Health and Wellness Products | B | Disagree | Neutral |
| Health and Wellness Products | C | Neutral | Agree |
| Marketing Services | B | Agree | Fully agree |
| Sustainable Fashion | A | Fully agree | Fully disagree |
| E-learning Platforms | D | Fully disagree | Disagree |
| Health and Wellness Products | E | Disagree | Neutral |
| Sustainable Fashion | F | Neutral | Agree |
| Marketing Services | G | Agree | Fully agree |
| Food delivery | H | Fully agree | Fully disagree |
| Smart home | I | Fully disagree | Fully agree |
| Smart home | J | Fully agree | Agree |
Hi Anonymous ,
When plotting a bar graph, it is mandatory for the other field to be an aggregation to display a bar.
Here, x-axis cannot be a text field when y-axis is already a Dimension field.
By using the measure provided you can show average rating by Business unit or you can make use of Small Multiples feature to have a clearer understanding of the data.Below snap for reference-
Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
7 Replies
Anonymous Assuming the numbers are mapped like this :
Fully Disagree -2 ,Disagree -1,Neutral 0,Agree 1,Fully Agree 2
You can do this using a calculated column ,that assigns a number to each category:LikertValue =
SWITCH(
[YourColumn],
"Fully Disagree", 1,
"Disagree", 2,
"Neutral", 3,
"Agree", 4,
"Fully Agree", 5
)
Now create a measure to calculate the average Likert score:AvgLikert = AVERAGE(YourTable[LikertValue])
Use the AvgLikert measure as your value. This will show the average score, but still allows you to use category labels for context.
Hope this helps !!! 😊
Anonymous Assuming you have mapped numbers to categories as :
Fully Disagree -2 ,Disagree -1,Neutral 0,Agree 1,Fully Agree 2Use the following to create a calculated column that assigns a number to each category :
LikertValue =
SWITCH(
[YourColumn],
"Fully Disagree", 1,
"Disagree", 2,
"Neutral", 3,
"Agree", 4,
"Fully Agree", 5
)
Now create a measure to calculate the average Likert score:
AvgLikert = AVERAGE(YourTable[LikertValue])This will show the average score, but still allows you to use category labels for context.
Hope this helps!!! 😊Hi Anonymous
What visual are you using? Also, please share sample data (not a screenshot), your expected result, and the reasoning behind. A link to an Excel file or a sanitized copy of your PBIX is fine (confidential data removed). Just as it takes time to prepare your sample data, it also takes time to come up with a solution.
I provided the dummy data in the original post.
I'm using the visual clustered bar chart.
The reasoning behind is to create a report in which people can see the results of a microsoft forms file in an easy way.
The goal is to create the visual like in the screenshot, and on the y-axis would be the business units and the colours would be the departments
Hi Anonymous ,
When plotting a bar graph, it is mandatory for the other field to be an aggregation to display a bar.
Here, x-axis cannot be a text field when y-axis is already a Dimension field.
By using the measure provided you can show average rating by Business unit or you can make use of Small Multiples feature to have a clearer understanding of the data.Below snap for reference-
Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.Hi Anonymous ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You
