Forum Discussion
Grouping Age by Age Brackets
Hi All,
I need to create the below chart in Power BI. I have a column that has a persons age, and need to show the total counts per age bracket. I tried to use dynamic segmentation however it's showing inflated totals, as my formula may be wrong. If I cannot use dynamic segmentation... is there another way to group the age column into age brackets? Thank you in advance!
Data Source = Salesforce
Field Used = Age
- Data Type = Decimal Number
- Format = General
This is the required chart needed in Power BI:
| Age | # of Names |
| 25 and under | 32,389 |
| 26-30 | 38,509 |
| 31-35 | 36,173 |
| 36-40 | 31,514 |
| 41-45 | 30,046 |
| 46-50 | 37,147 |
| 51-55 | 41,826 |
| 56-60 | 46,666 |
| 61-65 | 37,972 |
| 66+ | 58,103 |
| Unknown | 13,917 |
| Total | 404,262 |
I tried to use the dynamic segmentation dax formula (below) and created a new table (below), however I'm receiving the wrong totals per age bracket.
This is the New Table I created for the age bracket:
| Group | Min | Max |
| 25 and under | 0 | 25 |
| 26-30 | 26 | 30 |
| 31-35 | 31 | 35 |
| 36-40 | 36 | 40 |
| 41-45 | 41 | 45 |
| 46-50 | 46 | 50 |
| 51-55 | 51 | 55 |
| 56-60 | 56 | 60 |
| 61-65 | 61 | 65 |
| 66+ | 66 | 120 |
| Blanks |
This is the data I'm receiving, however my totals are not correct:
| Group | Age Bracket |
| 25 and under | 89052 |
| 26-30 | 10029 |
| 31-35 | 19521 |
| 36-40 | 26017 |
| 41-45 | 26862 |
| 46-50 | 34793 |
| 51-55 | 46662 |
| 56-60 | 65528 |
| 61-65 | 68849 |
| 66+ | 132671 |
I took a different approach, and added a custom column in Power Query. It creates a column with the Range descriptions in it, based on the employee's age.
4 Replies
Wait, so are you receiving raw Age data or no? If so, I would think that a SWITCH(TRUE()...) statement is your best bet.
I'm not 100% sure if I'm receiving the data from the age field...
For bucket (25 and under) I should see a counmt of 3106, and the number I'm receiving is 89052. I'm not sure I'm pulling from the age field. If I use the SWITCH(True) statement how would that look? Thanks very much for the quick reply!
I took a different approach, and added a custom column in Power Query. It creates a column with the Range descriptions in it, based on the employee's age.
