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.
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
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 |
Solved! Go to Solution.
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.
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.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |