topic Re: Grouping column in DAX Commands and Tips

Grouping column

Anonymous — Sun, 17 Apr 2022 16:02:49 GMT

Hi! I need to create a formula, when I will use an existing table but with grouping, how can I do it?

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 16:45:57 GMT

Hi Anonymous

this is a very general question. Would you please provide more details about your data structure and the expected results.

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 16:58:24 GMT

Remember, couple days ago I asked about this: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Creating-a-column-count-by-different-values-from-another-column/m-p/2459102#M66463
So, as example, now I created a column "score":

person_id	question	score
1	(first question)	0.21
2	(first question)	0.21
3	(first question)	0.21
1	(second question)	0.83
2	(second question)	0.83
3	(second question)	0.83
4	(first question)	0.35
5	(first question)	0.35
6	(firstquestion)	0.35

So, now I need to make a formula, that will sum up the score, per question, but, as I used COUNTROWS ( CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[Column1] ) ) ) to show the score, it shows the same one score per each person, but I don't need sum all 6 score per first question, I want to sum 0.21 + 0.35. Hope you got the problem, if not, I'll try to explain in a diff way.

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 17:05:01 GMT

Cognitive ease = SUMX(Filter(table , table[question] = "first_question"), table[score])

That is my formula, I need to some how add grouping that I need

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 17:08:44 GMT

Anonymous

By group you mean same question same score? How many groups there are for each question? Are trying to sum the distinct values of scores of the question groups?

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 17:13:21 GMT

Yes, I don't need to sum 3 times 0.21, it will be 0.63, but I need only 0.21. This score is question score, not persons, but it is shown per person, that's the problem

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 17:16:14 GMT

Anonymous

Please try this

Total Scores = SUMX ( DISTINCT ( SELECTCOLUMNS ( CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[question] ) ), "@question", [question], "@score", [score] ) ), [@score] )

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 17:25:16 GMT

It works for now, I need to do it couple times for some cases, but thanks a lot!!!

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 18:00:09 GMT

It's very good solution, but, what if instead of sum, I need average, when I just put averagex, it works wrongly

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 18:07:20 GMT

Anonymous

Yes exactly

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 18:12:27 GMT

but it doesn't work, when I just put averagex

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 18:21:35 GMT

Can you explain further with example? Also please confirm you are creating a calculated column not a measure

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 18:23:46 GMT

I'm creating a measure, sorry that I haven't mentioned. For sum it worked, for average not

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 18:29:53 GMT

Anonymous
I don't know how your visual look like but you may try

Average Scores = AVERAGEX ( DISTINCT ( SELECTCOLUMNS ( CALCULATETABLE ( ALL ( Table ), ALLEXCEPT ( Table, Table[question] ) ), "@question", [question], "@score", [score] ) ), [@score] )

Re: Grouping column

Anonymous — Sun, 17 Apr 2022 18:34:35 GMT

ALL() takes a column, not a table

Re: Grouping column

tamerj1 — Sun, 17 Apr 2022 18:36:50 GMT

It take either. But please re- copy the code as the location of all was wrong