Latest Enterprise DNA Initiatives


Dynamic Segmentation - Age Band - Headcount showing repeated values for each age band

Hi,

I posted a smilar post where I wanted help with a Dynamic Age Banding issue, which looks like it is now fixed.
The attached pbix file has a visual Clustered column chart (Headcount by Age Band) where I want to show the Headcount for each Age Band, this will also change results when the Years have been changed, but the Headcount is showing the same value of 11 for each Age Band.

Why is it not showing the what the headcount is for each Age band category?
Any ideas?

Thanks

Dynamic Calculate Age and Headcount.pbix (180.9 KB)

1 Like

Hi @denwo1982
Thank you very much for posting your query in the forum.
I have created the following measure to calculate the Number of Employees for each age range:

# of Employee by Age Band_JAFP =

VAR _CurrentAgeBand = MAX( 'Age Band Desc'[Age Band] )

VAR _EmployeeandAgeRange = ADDCOLUMNS(

    VALUES( Employee[Employment Id] ),

        "@Age Range", [Age Range_JAFP]

)

VAR _Result = COUNTROWS(

    FILTER(

        _EmployeeandAgeRange,

        [@Age Range] = _CurrentAgeBand

    )

)

RETURN

_Result



Age Range_JAFP =

VAR _Age = [Age]

VAR _Result = CALCULATE(

    VALUES( 'Age Band Desc'[Age Band] ),

        FILTER(

          'Age Band Desc',

          'Age Band Desc'[Start] <= _Age &&

         'Age Band Desc'[End] >= _Age

        )

)

RETURN

_Result

Regards,

Dynamic Calculate Age and Headcount_JAFP.pbix (190.9 KB)

Hi @jafernandezpuga

Thanks for your reply, I’ve had a look at your latest report but it is not returning the correct results I am expecting to see for the Headcount and what it is for that Age Band for that time of Year.

If you look at the below image that is what the headcount should be for each age band and for the year it is in
So for the Year 2022
I was expecting to see the below counts
β€œ20 - 30” = 4
β€œ31 - 40” = 5
β€œ41 - 50” = 2
and all the other age bandings would be zero

But on yours it is showing…
β€œ20 - 30” = 5
β€œ31 - 40” = 7
β€œ41 - 50” = 2

It looks like it is including in the Headcount the Employees that have Left (Employment End Date)

Age Band - Headcount Matrix

Thanks

1 Like

Hi @denwo1982 ,
I share a possible solution that I have been working on. I hope it can be of help to you.

Measures:

Age Range by Employee =

VAR _Employees =

CALCULATETABLE(

    VALUES( Employee[Employment Id] ),

      'Employee'[Employment Start Date] <= MAX( 'Calendar'[Date] ) &&

      (

            ISBLANK( 'Employee'[Employment End Date] ) ||

          'Employee'[Employment End Date] > MAX( Calendar[Date] )

        )

)

VAR _CurrentEmployee = MAX( Employee[Employment Id] )

VAR _Age = [Age]

VAR _Result = CALCULATE(

    VALUES( 'Age Band Desc'[Age Band] ),

        FILTER(

          'Age Band Desc',

          'Age Band Desc'[Start] <= _Age &&

         'Age Band Desc'[End] >= _Age

        )

)

RETURN

IF(

    _CurrentEmployee IN _Employees,

    _Result,

    BLANK()

)


# of Employee by Age Band Totals =

VAR _CurrentAgeBand = MAX( 'Age Band Desc'[Age Band] )

VAR _CurrentYear = MAX( 'Calendar'[Date].[Year] )

VAR _Employees = CALCULATETABLE(

    VALUES( Employee[Employment Id] ),

      'Employee'[Employment Start Date] <= MAX( 'Calendar'[Date] ) &&

      (

            ISBLANK( 'Employee'[Employment End Date] ) ||

          'Employee'[Employment End Date] > MAX( Calendar[Date] )

        )

)

VAR _EmployeesandAgeRange = FILTER(

    ADDCOLUMNS(

        CROSSJOIN(

            _Employees,

            VALUES( 'Calendar'[Date].[Year] )

        ),

        "@Age Range", [Age Range by Employee]

    ),

    NOT ISBLANK( [@Age Range] )

)

VAR _Result = SWITCH(

    TRUE(),

    ISINSCOPE( 'Age Band Desc'[Age Band] ) &&

    ISINSCOPE( 'Calendar'[Date].[Year] ),

    COUNTROWS(

        FILTER(

            _EmployeesandAgeRange,

            [@Age Range] = _CurrentAgeBand &&

          'Calendar'[Date].[Year] = _CurrentYear

        )

    ),

    NOT ISINSCOPE( 'Age Band Desc'[Age Band] ) &&

    ISINSCOPE( 'Calendar'[Date].[Year] ),

    COUNTROWS(

        FILTER(

            _EmployeesandAgeRange,

          'Calendar'[Date].[Year] = _CurrentYear

        )

    ),

    ISINSCOPE( 'Age Band Desc'[Age Band] ) &&

    NOT ISINSCOPE( 'Calendar'[Date].[Year] ),

    COUNTROWS(

        FILTER(

            _EmployeesandAgeRange,

            [@Age Range] = _CurrentAgeBand

        )

    )

)

RETURN

_Result + 0

Regards

Dynamic Calculate Age and Headcount_JAFP.pbix (177.3 KB)

Thanks @jafernandezpuga yes that looks good now, nice work! Excellent!

1 Like

Hi @jafernandezpuga how would I be able to drill through to the detail of the Employees that belong to that Age Band? Or is it not possible because it uses a Dynamic Segregation?
Would I have to redesign my model?

Hi @denwo1982,
We can create a Tooltip and in the Age by Employment graph, add the filter Age Range by Employee in not blank.

Regards,

Dynamic Calculate Age and Headcount_JAFP.pbix (176.8 KB)

1 Like