Use forms categories to calculate average

[irisoer]

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

[v-sdhruv]

Hi @irisoer ,
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

[v-sdhruv]

Hi @irisoer ,
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

[v-sdhruv · Accepted solution]

Hi @irisoer ,

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.

[danextian]

Hi @irisoer 

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.

[irisoer]

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

[Sonalika]

@irisoer  Assuming you have mapped numbers to categories as : 
Fully Disagree -2 ,Disagree -1,Neutral 0,Agree 1,Fully Agree 2

Use 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!!! 😊

[Sonalika]

@irisoer 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 !!! 😊