Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
// 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] )
)
)
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.
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!
"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]
)
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?
"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.
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.
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.
@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]))
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%.
This is my desired end result:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |