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

[afaherty]

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!

[Anonymous]

// 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] )
	)
)

[Anonymous]

@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.

[afaherty]

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!

[Anonymous]

"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]
)

[afaherty]

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?

[Anonymous]

"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.

[afaherty]

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.

[Anonymous]

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.

[amitchandak]

@afaherty , depending on where you are using

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

or

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

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

[afaherty]

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%.

[afaherty]

This is my desired end result: