Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BradleyN1
Frequent Visitor

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 😀

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

 

Dangar332_0-1698782782669.png

 

View solution in original post

6 REPLIES 6
Dangar332
Super User
Super User

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)

 

 

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! 

Hi, @BradleyN1 

 

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

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

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_0-1698782782669.png

 

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.