Grouping column

[Anonymous]

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

[tamerj1]

Hi @Anonymous 

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

[tamerj1]

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

[Anonymous]

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

[tamerj1 · Accepted solution]

@Anonymous 

Please try this

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

[Anonymous]

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

[tamerj1]

@Anonymous 

Yes exactly

[Anonymous]

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

[tamerj1]

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

[Anonymous]

ALL() takes a column, not a table

[tamerj1]

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

[tamerj1]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Remember, couple days ago I asked about this: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Creating-a-column-count-by-different-values-f...
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.