topic Re: Grouping Age by Age Brackets in DAX Commands and Tips

Grouping Age by Age Brackets

wnicholl — Mon, 18 Nov 2019 19:30:32 GMT

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

Re: Grouping Age by Age Brackets

Greg_Deckler — Mon, 18 Nov 2019 21:41:32 GMT

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

Re: Grouping Age by Age Brackets

wnicholl — Mon, 18 Nov 2019 21:51:22 GMT

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!

Re: Grouping Age by Age Brackets

nhoward — Tue, 19 Nov 2019 01:24:42 GMT

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.

Re: Grouping Age by Age Brackets

kwatson1 — Thu, 30 May 2024 17:09:41 GMT

This worked great, thanks for sharing!