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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wnicholl
Resolver II
Resolver II

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

4 REPLIES 4
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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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

This worked great, thanks for sharing!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors