Cost Per Headcount Measures and Analysis

You have learnt about the two major categories of Measures −

Finance Measures.

Headcount Measures.

The third major category of measures that you will learn is People Cost Measures. Any organization will be interested to know the annuapzed cost per head. Annuapzed cost per head represents the cost to the company of having one employee on a full year basis.

To create Cost Per Head measures, you need to first create certain prepminary People Cost Measures. In the Accounts table, you have a column – Sub Class that contains People as one of the values. Hence, you can apply a filter on the Accounts table on the Sub Class column to obtain the filter context onto the Finance Data table to obtain People Cost.

You can use thus obtain People Cost measures and Count of Months measures to create Annuapzed People Cost measures. You can finally create Annuapzed Cost Per Head measures from Annuapzed People Cost measures and Average Head Count measures.

Creating Actual People Cost Measure

You can create Actual People Cost measure as follows −

Actual People Cost:=CALCULATE( Finance Data [Actual Sum], FILTER( Finance Data , RELATED(Accounts[Sub Class])="People"))

Creating Budget People Cost Measure

You can create Budget People Cost measure as follows −

Budget People Cost:=CALCULATE( Finance Data [Budget Sum], FILTER( Finance Data , RELATED(Accounts[Sub Class])="People"))

Creating Forecast People Cost Measure

You can create Forecast People Cost measure as follows −

Forecast People Cost:=CALCULATE( Finance Data [Forecast Sum], FILTER( Finance Data , RELATED(Accounts[Sub Class])="People"))

Creating Annuapzed Actual People Cost Measure

You can create Annuapzed Actual People Cost measure as follows −

Annuapzed Actual People Cost:=IF([CountOfActualMonths],[Actual People Cost]*12/[CountOfActualMonths],BLANK())

Creating Annuapzed Budget People Cost Measure

You can create Annuapzed Budget People Cost measure as follows −

Annuapzed Budget People Cost:=IF([CountOfBudgetMonths], [Budget People Cost]*12/[CountOfBudgetMonths],BLANK())

Creating Annuapzed Forecast People Cost Measure

You can create Annuapzed Forecast People Cost measure as follows −

Annuapzed Forecast People Cost:=IF([CountOfForecastMonths],[Forecast People Cost]*12/[CountOfForecastMonths],BLANK())

Creating Actual Annuapzed Cost Per Head Measure

You can create Actual Annuapzed Cost Per Head (CPH) measure as follows −

Actual Annuapzed CPH:=IF([Actual Average Headcount], [Annuapzed Actual People Cost]/[Actual Average Headcount],BLANK() )

Creating Budget Annuapzed Cost Per Head Measure

You can create Budget Annuapzed Cost Per Head (CPH) measure as follows −

Budget Annuapzed CPH:=IF([Budget Average Headcount],[Annuapzed Budget People Cost]/[Budget Average Headcount],BLANK())

Creating Forecast Annuapzed Cost Per Head Measure

You can create Forecast Annuapzed Cost Per Head (CPH) measure as follows −

Forecast Annuapzed CPH:=IF([Forecast Average Headcount],[Annuapzed Forecast People Cost]/[Forecast Average Headcount], BLANK())

Creating Prior Year Actual Annuapzed Cost Per Head Measure

You can create Prior Year Actual Annuapzed Cost Per Head (CPH) measure as follows −

Prior Year Actual Annuapzed CPH:=CALCULATE([Actual Annuapzed CPH], DATEADD( Date [Date],-1,YEAR) )

Analyzing Data with Cost Per Head Measures

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows.

Add the measures Actual Annuapzed CPH, Budget Annuapzed CPH, and Forecast Annuapzed CPH to Columns.

Add the field Fiscal Year from Date table to Filters.

Select FY2016 in the Filter.

Create another Power PivotTable as follows −

Add the field Fiscal Quarter from Date table to Rows.

Add the measures Actual Annuapzed CPH, and Prior Year Actual Annuapzed CPH to Columns.

Insert a Spcer on the field Fiscal Year from Date table.

Select FY2015 and FY2016 on the Spcer.