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)

     

     

BradleyN1's avatar
BradleyN1
Frequent Visitor
2 years ago

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'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 🙂