cancel
Showing results for
Did you mean:

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

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 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
1 ACCEPTED SOLUTION
Resolver I

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.

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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!

Resolver I

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.