topic Re: Headcount growth rate in DAX Commands and Tips

Headcount growth rate

mariomyhanh — Mon, 06 Feb 2023 21:50:17 GMT

Good afternoon,

I'm trying to figure out a dax to calculate the growth % year over year for each dept with the sample query below.

I created a clustered column chart to show # of distinct position each year by department using a query with similar headings below.

I would like to create a formula to show growth % year over year within each dept when hovering over certain year (sample graph i would like to show below). Please help. Thank you

Re: Headcount growth rate

Jihwan_Kim — Mon, 06 Feb 2023 19:14:53 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Position # count: = COUNTROWS( VALUES(Data[Position #]) )

Position # count YoY: = VAR _currentyear = [Position # count:] VAR _prevyear = CALCULATE ( [Position # count:], FY[FY] = MAX ( FY[FY] ) - 1 ) RETURN DIVIDE ( _currentyear - _prevyear, _prevyear )

Re: Headcount growth rate

mariomyhanh — Mon, 06 Feb 2023 20:15:50 GMT

Is there a way to count position # using distinct count? for example, if there's a repeated position number in the same fiscal year to only count it once. I tried this forumula

DistinctCountofPosition = DISTINCTCOUNT('Staffing Roster'[Position])

and it works in a table, but when i try to add it to the dax provided for YoY% it will not work.

Position # count YoY: =

VAR _currentyear = [DistinctCountofPosition]

VAR _prevyear =

CALCULATE ( [DistinctCountofPosition], FY[FY]= MAX ( FY[FY] - 1 )

RETURN

DIVIDE ( _currentyear - _prevyear, _prevyear )

please help

Re: Headcount growth rate

Jihwan_Kim — Tue, 07 Feb 2023 03:56:22 GMT

Hi,

thank you for your message.

I am not sure but please check if you missed to insert ")" like the below.

Position # count YoY: = VAR _currentyear = [DistinctCountofPosition] VAR _prevyear = CALCULATE ( [DistinctCountofPosition], FY[FY] = MAX ( FY[FY] ) - 1 ) RETURN DIVIDE ( _currentyear - _prevyear, _prevyear )

Or, please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

Thanks.

Re: Headcount growth rate

mariomyhanh — Tue, 07 Feb 2023 15:43:15 GMT

Thank you so much for your help and patience. here is the link to the sample data. I hope you are able to access it.

Sample Headcount growth rate data.pbix

Re: Headcount growth rate

Jihwan_Kim — Tue, 07 Feb 2023 18:21:39 GMT

Hi,

Thank you for your link, but I cannot accesss to it.

Re: Headcount growth rate

mariomyhanh — Wed, 08 Feb 2023 12:47:35 GMT

Unfortunately, i am not able to share the file. So sorry. When adding the YoY to the value field table (see below) it returns 0 for all the FY YoY. . I hope this helps. Thank you so much again.

Re: Headcount growth rate

Jihwan_Kim — Wed, 08 Feb 2023 04:08:50 GMT

Hi,

I think my sample model is simpler than yours.

In order to get previous year's numbers in your model, please try using relevant column from Dim Date table.

Thanks.