Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Grouping column

Hi! I need to create a formula, when I will use an existing table but with grouping, how can I do it?
  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 

    Please try this

    Total Scores =
SUMX (
    DISTINCT (
        SELECTCOLUMNS (
            CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[question] ) ),
            "@question", [question],
            "@score", [score]
        )
    ),
    [@score]
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 

        Please try this

        Total Scores =
SUMX (
    DISTINCT (
        SELECTCOLUMNS (
            CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[question] ) ),
            "@question", [question],
            "@score", [score]
        )
    ),
    [@score]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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