Forum Discussion
Assign numeric value to a specific text string to get an average
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!
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!!
Solution SageHi 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
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
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!
Hi Bifinity,
This worked perfectly 👌😁
Thanks so much for your help and time!!