topic Re: DAX for survey results: % who answered "Strongly Agree" etc for each question in DAX Commands and Tips

DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Mon, 19 Jul 2021 20:51:07 GMT

Hello!

Brand new to DAX here.

Background: Using dummy survey data to learn DAX. There's 4 survey questions, scored on a Likert scale ("Strongly Agree", etc). I have already recoded the Likert scale answers into codes (a scale of 1 to 4). Here's the data for one of the questions. I am trying to insert a measure to calculate the % that answered with each possible response, for each question. My start with DAX has been rocky even though I am great with Excel functions! Thanks to anyone who might be able to help!

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

amitchandak — Tue, 20 Jul 2021 05:49:21 GMT

afaherty , depending on where you are using

divide(countrows(filter(Table, Table[response] =4)), countrows(Table))

divide(countrows(filter(Table, Table[response] =4)), countx(all(Table), Table[reponse]))

or
divide(countrows(Table), countx(all(Table), Table[reponse]))

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

Anonymous — Tue, 20 Jul 2021 13:08:19 GMT

afaherty

DAX is not Excel. It's far away removed from it in fact. Secondly, if you want to learn DAX, there's one book that teaches you all: "The Definitive Guide to DAX" by Alberto Ferrari and Marco Russo. Read it and you'll know DAX. Then practise it.

From your question it's not clear whether you're asking for a measure or a calculated column.

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Tue, 20 Jul 2021 13:11:57 GMT

I'm looking for a measure, as a column wouldn't make sense.

I have a book, and am reading it cover to cover while practicing but I am having a hard time catching on.

Thanks!

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

Anonymous — Tue, 20 Jul 2021 14:03:10 GMT

"I am trying to insert a measure to calculate the % that answered with each possible response, for each question."

This is your own words. If you try to INSERT a measure (into a table which you show) it means you really want a calculated column. And a calculated column can be created with such a definition, so it's not true it does not make sense.

But here's a measure, or rather a family of measures that depend on X.

// You have to substitute // the right piece of code // for X in the code below. // T is your table [Total Answers] = COUNTROWS( T ) [# Who Answered With X] = // measure var AnswerCode = X var Result = CALCULATE( [Total Answers], KEEPFILTERS( T[Response] = AnswerCode ) ) return Result [% Who Answered With X] = DIVIDE( [# Who Answered With X], [Total Answers] )

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Tue, 20 Jul 2021 16:01:20 GMT

I was hopeful about that last one on your list, but here's what happened once I applied it. it's taking the % of people who answered from the entire dataset, not by survey question. Each row should have a total of 100%, not 25%.

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Tue, 20 Jul 2021 16:22:46 GMT

I am brand new to this. Can you be a little genlter with me?

With all due respect, I don't agree that an inserted column makes sense.

This is my desired end result. Do I really have to ask it to calculate separately the % who answered Strongly Agree, then the % who answered Agree, etc?

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Tue, 20 Jul 2021 16:23:16 GMT

This is my desired end result:

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

Anonymous — Tue, 20 Jul 2021 16:38:19 GMT

// T is your table [Total Answers] = COUNTROWS( T ) [% of Answers] = DIVIDE( [Total Answers], CALCULATE( [Total Answers], // Response has to be the column // you place in the Columns well. Do // not use the raw codes. It'll stop // working then. Hide them as I don't // think anyone would like to see // them since they're meaningless // to the business user. ALLSELECTED( T[Response] ) ) )

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

Anonymous — Tue, 20 Jul 2021 16:37:13 GMT

"With all due respect, I don't agree that an inserted column makes sense."

Whether or not something makes sense depends solely on its definition (and your understanding of it). I sent you the right measure in the post preceding this one.

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

afaherty — Wed, 21 Jul 2021 17:01:05 GMT

I discovered that amitchandak 's solution below DIVIDE(COUNTROWS(Sheet1),COUNTX(ALL(Sheet1),Sheet1[Response])) does work, I just have to set the result to "Show value as percent of row total" and then it's perfect.

Re: DAX for survey results: % who answered "Strongly Agree" etc for each question

Anonymous — Wed, 21 Jul 2021 16:55:54 GMT

DIVIDE( COUNTROWS( T ), COUNTROWS( ALL( T ) ) )

This is equivalent to amitchandak's formula but these formulas are absolute, not relative. It might be what you want but it might not be.