Forum Discussion
Advocate IICalculate Employees Turnover
Microsoft Employee
Acutally a preview feature:inline hierarchy has been instroduced in March 2016's release. Please check the below link
Use inline hierarchy labels in Power BI Desktop
If you have any question, feel free to let me know.
EZgraftonThank you Eric. The inline hierarchy labels are very useful. However, I still cannot figure out how to use them to calculate and visulize the turnover through the five levels of hierarchy. :catmad:
In other words, how should I build my DAX function to make it work?
Thank you
- inetguru
Advocate I
Hello.
In my dataset i use this base mesures:
1) To count headcount per day and then average from it. For one day the result will be the sum, but for any wider segments it will be average.
Average Headcount :=
CALCULATE (
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Date] );
"HC"; SUMX (
FILTER (
'Base';
'Base'[Start date of the period] <= 'Calendar'[Date]
&& 'Base'[End date of the period] >= 'Calendar'[Date]
);
'Base'[Headcount]
)
);
[HC]
);
ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] )
)I use ALL ( 'Base'[Company attitude to dismissal]; 'Base'[Reason for dismissal] )
to calculate the average number in any sections, but without taking into account the details on the fields 'Base'[Company attitude to dismissal] (regret, non regret) and 'Base'[Reason for dismissal].
2) To count the average for the rolling year
a) in Calendar table add column to count day number from the calendar start date:
SequentialDayNumber
=
COUNTROWS (
FILTER (
ALL ( 'Calendar' );
'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] )
&& NOT (
MONTH ( 'Calendar'[Date] ) = 2
&& DAY ( 'Calendar'[Date] ) = 29
)
)
)In my calendar there are all dates from the beginning of the year the minimum date of hiring to the end date of the analyzed year. In practice, for each frame move I received a new line with the date of the beginning of the event, the date of completion of the event, the date of hiring (not tied to the dates of the event, only to the current period of work in the company) and the date of dismissal (if not dismissed, date = 01.01 .2099).
b) to count rolling year:
Average Headcount rolling year :=
CALCULATE (
[Average Headcount];
FILTER (
ALL ( 'Calendar' );
'Calendar'[SequentialDayNumber]
> MAX ( 'Calendar'[SequentialDayNumber] ) - 365
&& 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
)
)3) To count numbers of dismissed for each day and sum for the period:
Number of Dismissed :=
SUMX (
ADDCOLUMNS (
VALUES ( 'Calendar'[Date] );
"Dismissed"; SUMX (
FILTER (
'Base';
'Base'[Start date of the period] <= 'Calendar'[Date]
&& 'Base'[End date of the period] >= 'Calendar'[Date]
&& 'Base'[Date of dismissal] = 'Calendar'[Date]
);
'Base'[Headcount]
)
);
[Dismissed]
)4) To count number of dismissed rolling year:
Number of Dismissed rolling year :=
CALCULATE (
[Number of Dismissed];
FILTER (
ALL ( 'Calendar' );
'Calendar'[SequentialDayNumber]
> MAX ( 'Calendar'[SequentialDayNumber] ) - 365
&& 'Calendar'[SequentialDayNumber] <= MAX ( 'Calendar'[SequentialDayNumber] )
)
)5) To count Turnover rolling year:
Staff turnover rolling year :=
IFERROR (
[Number of Dismissed rolling year] / [Average Headcount rolling year];
0
)