Counting values based on unique ID then dividing

[BradleyN1]

Hi,

I am working on survey data, and I want an overall satisfaction percentage.

As I've had to unpivot the columns, I've got duplicate statements in 1 column, but I only want to count the number of Strongly Agrees and Agrees based on the unique IDs (not multiple times they appear which will give me higher positives figures)

So for example: for ID 1, my output would be S1 = Agree and S2 = Strongly Agree which is 2 positive values, then I'd want to divide those figures by all the responses to each statement (there's actually 11 statements).

Here is an example:

ID	Statement	Response
1	Statement 1	Agree
1	Statement 1	Agree
1	Statement 2	Strongly Agree
1	Statement 2	Strongly Agree
2	Statement 1	Agree

Thanks 😀

[Dangar332]

Hi, @BradleyN1 

as i understand your view try below

just adjust your table and column name 

Measure 2 = 
var a = CALCULATE(DISTINCTCOUNT('Table (2)'[Response]),'Table (2)'[Response]=max('Table (2)'[Response]))
var b =  CALCULATE(COUNT('Table (2)'[Response]),'Table (2)'[Response]=max('Table (2)'[Response]))
return 
DIVIDE(a,b)

[BradleyN1]

Sorry, I should've said my Response column also has 'Disagree' and 'Strongly Disagree' in them. So, I need to pick out the 'Agree' and 'Strongly Agree'.

Sorry about that! 

[Dangar332]

Hi, @BradleyN1 

can you paste data with Disagree' and 'Strongly Disagree' value?

[BradleyN1]

It could be like this:

ID	Statement	Response
1	Statement 1	Agree
1	Statement 1	Agree
1	Statement 2	Strongly Agree
1	Statement 2	Strongly Agree
1	Statement 3	Disagree
1	Statement 3	Disagree
1	Statement 4	Strongly Disagree
1	Statement 4	Strongly Disagree
2	Statement 1	Strongly Agree
2	Statement 1	Strongly Agree
2	Statement 2	Disagree
2	Statement 2	Disagree
2	Statement 3	Agree
2	Statement 3	Agree
2	Statement 4	Disagree
2	Statement 4	Disagree

So per ID count each statement response (only Strongly Agree or Agree [positives]) then dividing by all of them per ID

[Dangar332 · Accepted solution]

Hi, @BradleyN1 

try below 

just adjust your table and column name

Measure = 
var a = CALCULATE(COUNT('count'[Response]),or('count'[Response]="agree" ,'count'[Response]="strongly agree"))
var b = COUNT('count'[Response])
return 
DIVIDE(a,b)

[BradleyN1]

Oh, you are amazing! It works perfectly, thanks so much 🙂