Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
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
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
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
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |