Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Group by group categorization

Is there a good formula or new table to create new groups based on categorization?  I'm trying to show a count or percent of total households into categorizations of no cost, less that $1000, between $1,000 and $2,000, between $2000 and $5,000, and greater than $5,000.  I'm really struggling with the DAX on this.  I have a table "Totals Table" with columns Address and Cost Per Household. 

 

  **I know you can create groups in visuals, but the data set is growing everyday and using that feature would require me to assign new records to the various groups each day**

 

Thank you!! 

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous,

    Based on my test, you could refer to below steps:

    Create a calculated column:

    Column = IF('Sheet1'[Total Cost]<1000,"Less than 1000",
         IF('Sheet1'[Total Cost]<=1999,"1000-1999",
         IF('Sheet1'[Total Cost]<=2999,"2000-2999",
         IF('Sheet1'[Total Cost]<=3999,"3000-3999",
         IF('Sheet1'[Total Cost]<=4999,"4000-4999",
             "Grate than 5000")))))

    Create a new table:

    Table = SUMMARIZE('Sheet1','Sheet1'[Column],"Total number of properties",CALCULATE(COUNT(Sheet1[Column]))) 

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Here is a link to the data 

       

      https://1drv.ms/u/s!AglKBF5k_MzKgS2KVIF60yRIVsFQ

       

       

      I'm basically trying to create a new table that shows the following. It's counting the number of addresses with a cost in each of those ranges. I was having trouble figuring that out through the link you shared. 

       

      Cost of Repair: Total number of properties 

      Less than 1000: 20
      1000-1999: 7
      2000-2999: 8
      3000-3999: 7
      4000-4999: 4
      Greater than 5000: 3

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

        Based on my test, you could refer to below steps:

        Create a calculated column:

        Column = IF('Sheet1'[Total Cost]<1000,"Less than 1000",
         IF('Sheet1'[Total Cost]<=1999,"1000-1999",
         IF('Sheet1'[Total Cost]<=2999,"2000-2999",
         IF('Sheet1'[Total Cost]<=3999,"3000-3999",
         IF('Sheet1'[Total Cost]<=4999,"4000-4999",
             "Grate than 5000")))))

        Create a new table:

        Table = SUMMARIZE('Sheet1','Sheet1'[Column],"Total number of properties",CALCULATE(COUNT(Sheet1[Column]))) 

        Result:

        You could also download the pbix file to have a view.

         

        Regards,

        Daniel He