topic Counting values based on unique ID then dividing in DAX Commands and Tips

Counting values based on unique ID then dividing

BradleyN1 — Tue, 31 Oct 2023 16:27:28 GMT

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:

ID	Statement	Response
1	Statement 1	Agree
1	Statement 1	Agree
1	Statement 2	Strongly Agree
1	Statement 2	Strongly Agree
2	Statement 1	Agree

Thanks 😀

Re: Counting values based on unique ID then dividing

Dangar332 — Tue, 31 Oct 2023 18:12:58 GMT

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)

Re: Counting values based on unique ID then dividing

BradleyN1 — Tue, 31 Oct 2023 19:18:59 GMT

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!

Re: Counting values based on unique ID then dividing

Dangar332 — Tue, 31 Oct 2023 19:30:08 GMT

Hi, BradleyN1

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

Re: Counting values based on unique ID then dividing

BradleyN1 — Tue, 31 Oct 2023 19:43:32 GMT

It could be like this:

ID	Statement	Response
1	Statement 1	Agree
1	Statement 1	Agree
1	Statement 2	Strongly Agree
1	Statement 2	Strongly Agree
1	Statement 3	Disagree
1	Statement 3	Disagree
1	Statement 4	Strongly Disagree
1	Statement 4	Strongly Disagree
2	Statement 1	Strongly Agree
2	Statement 1	Strongly Agree
2	Statement 2	Disagree
2	Statement 2	Disagree
2	Statement 3	Agree
2	Statement 3	Agree
2	Statement 4	Disagree
2	Statement 4	Disagree

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

Re: Counting values based on unique ID then dividing

Dangar332 — Tue, 31 Oct 2023 20:06:30 GMT

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)

Re: Counting values based on unique ID then dividing

BradleyN1 — Tue, 31 Oct 2023 20:41:47 GMT

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