Group by group categorization

[Anonymous]

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!! 

[TomMartens]

Hey,

I guess these patterns are helpful:

Dynamic Segmentation
https://www.daxpatterns.com/dynamic-segmentation/

or

Static Segmentation

https://www.daxpatterns.com/static-segmentation/

If you need more help, please consider to create a little pbix file with sample data, upload the data to onedrive or sharepoint and upload the link.

Regards,

Tom

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

Thank you! This worked perfect! I think I was overcomplicating it with the formulas I was trying. The table description helps too, but I was planning to acheive that in the visual, I guess I should have clarified that.

Thank you again for your help 🙂