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

15 Replies

  • 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

         

    • 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