Assign numeric value to a specific text string to get an average

[Pamdelorbe]

Hi everyone!

I'm analyzing the answers of a survey in order to get the general score of each response.  The issue is that the responses are text strings. Por example "Excellent, "very good", "good", etc". 

I guess that first I have to assign a numeric value to each text string. For example, Excellent =5, Very good =4, Good =3, etc. I already try to create a new conditional column but didn't work since I need to multiply the total the count of response by the value assigned to get the total score of each value (i.e. Excellent score =18 x 5). 

For example, This is how data looks like. 

What I need is to create a formula that multiply each response (i.e excellent =18) by the assigned value (i.e = 5) then divided the sum of total assigned value by the sum of total count responses in order to get the average score (see below).

Parking facilites	Count of Parking facilites (responses)	CP Parking Facilities (assigned value)	Total
Excellent	18	5	90
Very good	15	4	60
Good	15	3	45
Fair	7	2	14
Poor	1	1	1
Did not use/ notice	1	0	0
SUM	57		210
AV. SCORE	3.68	Sum of total (210) / sum of count responses (57)

Any help will be more than welcome!

I'm super new in power Bi really appreciate very detailed responses. 

Thanks in advance!!

[Bifinity_75 · Accepted solution]

Hi @Pamdelorbe , try this:

-  Table 1 is something like this

- Table 2 is something like this

- Create this relationship:

- Create this calculate column:

- Create this measure:

- Create another this measure:

- Create the measure to the average:

The result:

I hope you works it, Best regards

[Bifinity_75]

The second measure you don't have to create, it's the calculated column:

[Pamdelorbe]

Hi Binifity, 

That was so so helpful! Thanks for take the time to answer with so much details. Millon thanks!!!

I just have one more quest. How can I make this scalable for the rest of my survey answers? Do i need to create a calculated colum and both measures for each response... For example this was for parking facilites and I want to get the average result from parking accesability, value for money, etc (same answers range apply: "Excellent", "Very good", "Good", etc..)

Million thanks in advance!! 

[Bifinity_75]

Hi Pambdelorbe, it is fully scalable.  The only requirement is that the source tables must have the same structure, and the name of the columns must remain the same as long as they are referenced in power query, for example for a type change:

Original data source (with another name of column):

And error in Power Query, because the column name has changed to apply the type change:

If in power query you have not applied any type of change referring to the columns, and the structure of the table is the same, you can scale it without problems.

I hope I have helped you, greetings

[Pamdelorbe]

Hi Bifinity, 

I tried to do it for the rest of the responses but unfortunately returning the same value as in "Park Facilities" for "Value for money" and "accessibility".  

the calculated colums are returning the same value as "Park Facilities"

It might be related to the relationship between tables?

Here the desired values:

Thanks so much in advance for your time and help! 

Have a great day!

[Bifinity_75 · Accepted solution]

Hi @Pamdelorbe , you can remove the relationships between the 2 tables, and follow this steps:

- Create (for example) this calculate columns:

The result is this:

- Create the measures to counts and averages:

The result is:

- When you insert the filelds (columns) to design the table, choose Don´t resume in this 2 column:

If you have problems let me know, 

Best regards!

[Pamdelorbe]

Hi Bifinity, 

This worked perfectly 👌😁

Thanks so much for your help and time!! 

[Bifinity_75 · Accepted solution]

Hi @Pamdelorbe , try this:

-  Table 1 is something like this

- Table 2 is something like this

- Create this relationship:

- Create this calculate column:

- Create this measure:

- Create another this measure:

- Create the measure to the average:

The result:

I hope you works it, Best regards