Forum Discussion
Assign numeric value to a specific text string to get an average
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!!
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
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!
7 Replies
- Bifinity_75
Solution Sage
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:
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
