Grouping Age by Age Brackets

[wnicholl]

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

[Greg_Deckler]

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

[wnicholl]

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!      

[nhoward · 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.  

[kwatson1]

This worked great, thanks for sharing!