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

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

 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 

Yes exactly

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

        ALL() takes a column, not a table