Forum Discussion

psilos24's avatar
psilos24
Helper I
6 years ago
Solved

Group values using a measure ?

Hello guys, I would like to ask if it is possible to group values using a measure. I know it is easy through a new column or right-click to the graph and choose the group values. The reason I'm asking is that I haven't access to transform the data and the only option is through the new measure. Any suggestions? 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    psilos24 
    Lets say if you have a column has value from 1-200, you want to group into 4 groups. You can create 4 measures and put them in the Values section.

     

    Group 1 = Caluclate(sum([Column]),filter(Table, [column]<=50)
Group 2 = Caluclate(sum([Column]),filter(Table, [column]>50&&[column]<=100)
Group 3 = Caluclate(sum([Column]),filter(Table, [column]>100&&[column]<=150)
Group 4 = Caluclate(sum([Column]),filter(Table, [column]>150&&[column]<=200)

     


    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

    • psilos24's avatar
      psilos24
      Helper I
      6 years ago

      amitchandak Thanks for the reply. Please find below a picture from my graph as an example. I want to make bins or groups for the x-axis. I can only use a measure. Any ideas? 

       

       

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    psilos24 You could write a measure to do that, but then it would have limited usability, as in you can't put a measure in many places, such as a legend for example. But it would work for a table just fine.

     

    Grouping Measure = if(sumx(Table,Table[Value])>1000,"High","Low")

     

    If you are really just using a small part of the overall dataset to create a specific report or chart, you could write a query on the data model and then import that in to a new report. Then you can create whatever you want, groups, calculated columns, you name it. I'd suggest connecting to the data model in DAX Studio and writing the query there, typically in the form of EVALUATE(SUMMARIZECOLUMNS(....)).

     

     

    • psilos24's avatar
      psilos24
      Helper I
      6 years ago

      DataZoe Thanks for the reply. Yes, I want to use the measure as a legend if it is possible, and in that way, it can show the bins-groups. Unfortunately, I can't write a query to connect my data model to DAX studio. Any other ideas? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    psilos24 

    It is not possible to add a measure as chart legend, as measure actually returns a single value.

     

    As an alternative, I would suggest you create two separately measures for each group(group 1=> less than 10, group 2 = between 10-20,etc). Then add these two measures into Values section of the chart.


    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • psilos24's avatar
      psilos24
      Helper I
      6 years ago

      Anonymous  Thanks for the reply. Can you give me an example of how to create a measure for a group?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        psilos24 
        Lets say if you have a column has value from 1-200, you want to group into 4 groups. You can create 4 measures and put them in the Values section.

         

        Group 1 = Caluclate(sum([Column]),filter(Table, [column]<=50)
Group 2 = Caluclate(sum([Column]),filter(Table, [column]>50&&[column]<=100)
Group 3 = Caluclate(sum([Column]),filter(Table, [column]>100&&[column]<=150)
Group 4 = Caluclate(sum([Column]),filter(Table, [column]>150&&[column]<=200)

         


        Paul Zheng _ Community Support Team
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.