Complex scoring from form questions

[knowledgesearch]

I have a form survey that asks questions based on talent some are strongly agree, to strong disagree statements however, some have answers that are statements and there is scoring based on how many skills that a person selects that their staff possess such as 1-2 skills equals a scoring of 1, or 3-4 skills equals a scoring of 2. 

[knowledgesearch]

I've put an extra table in with the responses along with a scoring, then unpivoted all the questions in the previous table. However, the scoring isn't totaling up correctly with the following measurements added 

Responses = DISTINCTCOUNT(Survey[Response ID])+0

Response % = DIVIDE([Responses],CALCULATE([Responses],ALLEXCEPT(Survey,Survey[Question])))

Custom Table Values = switch(true(),

ISINSCOPE(Weighing[Response]),[Responses] & if([Responses]=0,blank()," [" & format([Response %],"0%") & "]"),

[Weighted Average])

Weighted Average = AVERAGE(Survey[Responses - Copy])

Test Results 

[knowledgesearch]

ResponseScoring

Strongly Disagree	1
Disagree	2
Neutral	3
Agree	4
Strongly Agree	5
Foundational - satisfies the fundamental talent management processes, no automation of workflows and the hiring process is not measurable	1
Accelerating - regularly optimise and measure the recruitment process and have dedicated DEI and candidate hiring experience goals.	2
Dynamic - you have hiring and recruiting processes that can be measured and optimised to help make data-driven decisions at a tactical level.	3
Strategic - routinely hires high-quality candidates, but lacking in proactively planning for organisational shifts for future growth.	4
Transformational - future forward capabilities that allows the business to develop and growth according to strategic goals. Data-backed hiring and DEI initiatives that helps drive organisational performance and health. Automation of workflows are well structured and proven.	5
We have an overwhelmed and understaffed talent team trying to get data to use	1
A small team, that creates and updates reports annually	2
We have a dedicated team whose job is to maintain our data and help our recruiting	3
Our team has widely accessible data and proactively uses it for headcount planning to keep up ahead with hiring	4
Our team has full access to our TA data and uses it to improve our hiring processes and uses it to keep an eye on the future developing, forecasting and risks of our area	5
We hire whenever new business needs arise	1
We revise our hiring plan yearly to align with business objects	2
We have an effective annual strategy but are still more reactive rather than proactive when it comes to hiring	3
Were hiring proactively and have strong plans in place to meet current and future talent goals	4
Our strategy is future forward, forecasting areas of talent risk. We are competing for top tier talent.	5
We make regularly progress with reporting, and TA measure goals	1
We have effective goal-setting and reporting capabilities and our analysis of our TA efforts steadily improve	2
We're more reactive than proactive, our data is all together in spreadsheets and we aren't confident of our data.	3
We're making progress with sharing performance data with leaders, especially as it relates to reporting and measuring goals	4
A dedicated person/team owns talent analytics and regularly drives clear and definitely insights that we use to achieve current and future goals	5
No, they don't have clear roles or responsibilities	1
Yes, we have the roles, but responsibilities are not clear tactically or strategically	3
Yes, roles and responsibilities are clear, documented and defined	5
No tools or support	0.5
Data Visualisation	1.5
Data Analysis	1.5
Data Storage	1
Data Collection	1

[lbendlin]

you'll need to do some input data scrubbing before this may be useful. DAX is notoriously bad at doing fuzzy joins (but at least it's not case sensitive).

[knowledgesearch]

The issue I'm having is if you look at the raw data, all the data should be grouped by the respondent ID and I should be able to drill down of the area of the business and then by portfolio (its a branching in forms) but for some reason, when using a group by or duplicating the data it doesn't allow for a drill down other than simply putting a filter on to include functions. I'm wanting to do a full maturity on all areas of the business and create a scoring based from the scoring table, but when each skill is inputting it does not allow them to be added up using the custom table variables. Any solutions on how to get the data to be grouped and the measure to calculate each answer when there could be multiple selected on a field?

[lbendlin]

you'll need to do some input data scrubbing before this may be useful.

[knowledgesearch]

I've scrubbed the data and unpivotted all other columns that I've needed to be drilled down, and the scorecard all the data has been cleansed and matching up. However, with the custom variables measure I've created, it's still finding it difficult to add up items that are on the same field as a multiple choice. Any ideas on how to add multiple choices to the scoring card other than splitting the columns up, as it should be an area all on it's own or is it not possible. 

[lbendlin]

you could create ever more elaborate parsing rules. This means permanent maintenance as your users will continue to find ingenious ways to break your existing rules.

Or you could consider using a different tool - one that is better suited to handle your unstructured data.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?