Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wnicholl
Resolver I
Resolver I

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. 

 

Age Bracket =
CALCULATE ( [Name count],
        FILTER ( VALUES ( 'Contact (2)'[Age__c] ),
              COUNTROWS (
                          FILTER (
                                 'Age Bracket Group',
                                 'Contact (2)'[Age__c] >= 'Age Bracket Group'[Min]
                                  && 'Contact (2)'[Age__c] < 'Age Bracket Group'[Max] ))
                               > 0 ))

   

This is the New Table I created for the age bracket:

Group    MinMax
25 and under025
26-302630
31-353135
36-403640
41-454145
46-504650
51-555155
56-605660
61-656165
66+66120
Blanks  

 

This is the data I'm receiving, however my totals are not correct:

GroupAge Bracket
25 and under89052
26-3010029
31-3519521
36-4026017
41-4526862
46-5034793
51-5546662
56-6065528
61-6568849
66+132671
1 ACCEPTED 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.  

 

 
 

image.png

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Wait, so are you receiving raw Age data or no? If so, I would think that a SWITCH(TRUE()...) statement is your best bet.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.  

 

 
 

image.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors