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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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.


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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