Hello! I'm struggling to create a dynamic histogram that will show the distribution of the ages of all active employees based on selected filtered year.
I created a [Age] measure to dynamically calculate the age of all active employees in a filtered year:
Age =
VAR _selyear =
MAX(_Calendar[Year])
VAR _age =
CALCULATE(
DATEDIFF(SELECTEDVALUE(hr_coredata[DOB]),DATE(_selyear, 12, 31 ), YEAR ),
ALL(_Calendar),_Calendar[Year]<=_selyear)
RETURN
_age
I then created a table called _agebucket so I could categorize the ages of the employees.
_agebucket =
DATATABLE (
"Start Limit", INTEGER,
"End Limit", INTEGER,
"Bucket", STRING,
"Sort Order", INTEGER,
{
{ 1, 24, "Under 25", 1 },
{ 25, 34, "25-34", 2 },
{ 35, 44, "35-44", 3 },
{ 45, 54, "45-54", 4 },
{ 55, 80, "55+", 5 }
}
)
Then, I created a [Age Class] measure to categorize the ages of the employees:
Age Class =
MAXX(
FILTER(_agebucket, [Age] >= _agebucket[Start Limit] && [Age] <= _agebucket[End Limit]),
_agebucket[Bucket]
)
Finally, I created the [Age Bucket] measure to count the number of active employees per age class:
Age Bucket =
CALCULATE (
COUNTX (
FILTER (
VALUES (hr_coredata[Employee_Name] ),
[Age] >= MIN ( _agebucket[Start Limit] )
&& [Age] < MAX ( _agebucket[End Limit] )
),
hr_coredata[Employee_Name]
)
)
I created visual tables as shown below. My problem is the count of active employees per class is not accurate with the count in my age bucket. I'm not really sure what went wrong and I'm hoping someone can help me point it out.
You can see the .pbix file uploaded here for reference.
Thank you so much!