Forum Discussion

BradleyN1's avatar
BradleyN1
Frequent Visitor
2 years ago
Solved

Counting values based on unique ID then dividing

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 numbe...
  • Dangar332's avatar
    Dangar332
    2 years ago

    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)

     

     

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

Hi, BradleyN1 

 

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

BradleyN1's avatar
BradleyN1
Frequent Visitor
2 years ago

It could be like this:

IDStatementResponse
1Statement 1Agree
1Statement 1Agree
1Statement 2Strongly Agree
1Statement 2Strongly Agree
1Statement 3Disagree
1Statement 3Disagree
1Statement 4Strongly Disagree
1Statement 4Strongly Disagree
2Statement 1Strongly Agree
2Statement 1Strongly Agree
2Statement 2Disagree
2Statement 2Disagree
2Statement 3Agree
2Statement 3Agree
2Statement 4Disagree
2Statement 4Disagree

 

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

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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's avatar
      BradleyN1
      Frequent Visitor
      2 years ago

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