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 

 

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