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 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:

 

IDStatementResponse
1Statement 1Agree
1Statement 1Agree
1Statement 2Strongly Agree
1Statement 2Strongly Agree
2Statement 1Agree
   

 

Thanks ðŸ˜€

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

     

     

6 Replies

  • 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!