Calculating a 'quality score' from likert scale survey responses

[orinocoworkflow]

My math brain is being pushed to its limit on this one so hoping someone can help. I've been poking around the board for a few weeks but I haven't been able to find the answer I need.

I have to write a formula in PowerBI to give me a ‘quality score’ from likert scale survey responses. The formula I need to write is:

Quality score = % of agrees (agree and strongly agree)
                           Total number of responses

Because I have to be able to cut it by a number of factors (department name, as per the dummy data below, and others to follow), it makes more sense for it to be a measure in PowerBi attached to a visual instead of something built into my alteryx workflow (each line by itself cannot have its own quality score, the score can only be calculated when looking at a group).

In this example, the quality score for Crockery would be 71.4% and for Homewares it would be 85.7%

Here is the type of data I'm looking at:

RecordID	Department	Sub-department	Section	Name	Value
581	Homewares	Dining	Crockery	a. Good product selection	Agree
581	Homewares	Dining	Crockery	b. Had products suited to my needs	Agree
581	Homewares	Dining	Crockery	c. Had helpful employees	Agree
581	Homewares	Dining	Crockery	d. Easy to find what I needed	Neutral
581	Homewares	Dining	Crockery	e. Prices were fair	Agree
581	Homewares	Dining	Crockery	f. Good quality of products	Agree
581	Homewares	Dining	Crockery	g. Wait time to check out was good	Neutral
582	Clothing	Shoes	Kid's shoes	a. Good product selection	Strongly Agree
582	Clothing	Shoes	Kid's shoes	b. Had products suited to my needs	Neutral
582	Clothing	Shoes	Kid's shoes	c. Had helpful employees	Strongly Agree
582	Clothing	Shoes	Kid's shoes	d. Easy to find what I wanted	Agree
582	Clothing	Shoes	Kid's shoes	e. Prices were fair	Agree
582	Clothing	Shoes	Kid's shoes	f. Good quality of products	Strongly Agree
582	Clothing	Shoes	Kid's shoes	g. Wait time to check out was good	Agree
605	Homewares	Kitchen	Saucepans	a. Good product selection	Strongly Agree
605	Homewares	Kitchen	Saucepans	b. Had products suited to my needs	Strongly Agree
605	Homewares	Kitchen	Saucepans	c. Had helpful employees	Strongly Agree
605	Homewares	Kitchen	Saucepans	d. Easy to find what I wanted	Strongly Agree
605	Homewares	Kitchen	Saucepans	e. Prices were fair	Strongly Agree
605	Homewares	Kitchen	Saucepans	f. Good quality of products	Strongly Agree
605	Homewares	Kitchen	Saucepans	g. Wait time to check out was good	Strongly Agree

I appreciate any help you have to offer.

[orinocoworkflow]

Great, thank you!! I will carve out some time this week to try to get it working with my data set. I'm still a beginner so it may take me awhile to get this running but I will accept the solution if I can get it going.

[Ritaf1983]

Hi @orinocoworkflow 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[orinocoworkflow]

Thanks, I couldn't figure out how to add data in. I've now been able to add it in as a table - I hope it's coming across in an acceptable format.

[Ritaf1983 · Accepted solution]

Hi @orinocoworkflow 
I don't sure that understood correctly the desired result so created 2 versions with a similar basis. 
I created 3 measures :
1. Basic count of agree +strongly agree

Agree_st = CALCULATE(COUNTROWS('Table'),'Table'[Value]="agree"||'Table'[Value]="strongly agree")

2. count the percent by department:

%_agree_by_department = DIVIDE(CALCULATE([Agree_st],ALLSELECTED('Table'[Section])),CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'[Section])))

3. 

%_agree_by_section = DIVIDE(CALCULATE([Agree_st],ALLSELECTED('Table'[Department])),CALCULATE(COUNTROWS('Table'),ALLSELECTED('Table'[Department])))

this is good enough for a "flat" visual 

If you want something hierarchical you can use measures like:

%agree_by_hierarchy = if(ISINSCOPE('Table'[Section]),[%_agree_by_section],[%_agree_by_department])

The result :

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[orinocoworkflow]

This is along the lines of what I need but I'm wondering if there is a formula that can encompass any other variable I may add in the future. For example, if I add a column for Salesperson or day of the week, I would want a measure that could calculate quality scores for those variables as well, without me having to input an additional formula for each new column I may add.

If this is not something you think is possible, please let me know.

I appreciate you taking the time to have a go.

Andrea

[Ritaf1983]

Hi @orinocoworkflow 
If the goal is to show the measure based on the categorical column that you put on the visual you can select the column via field parameters, using a similar logic that I showed with isincope 

with selectedvalue based on the parameter (column) that you put on the visual.

more information about field parameters by the link:

https://www.youtube.com/watch?v=TLiqqIsk1CU&t=16s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly