Average Headcount Measures

In the previous chapter, you have learnt how to calculate ending headcounts for a specific period. Likewise, you can create the average monthly headcount for any given selection of months.

The Average Monthly Headcount is the sum of the monthly headcounts spanided by the number of months in the selection.

You can create these measures using DAX AVERAGEX function.

Creating Actual Average Headcount Measure

You can create Actual Average Headcount measure as follows −

Actual Average Headcount:=AVERAGEX(VALUES( Finance Data [Fiscal Month]), [Actual Ending Head Count])

Creating Budget Average Headcount Measure

You can create Actual Average Headcount measure as follows −

Budget Average Headcount:=AVERAGEX(VALUES( Finance Data [Fiscal Month]), [Budget Ending Head Count])

Creating Forecast Average Headcount Measure

You can create Forecast Average Headcount measure as follows −

Forecast Average Headcount:=AVERAGEX( VALUES( Finance Data [Fiscal Month]), [Actual Ending Head Count])

Creating Prior Year Actual Average Headcount Measure

You can create Prior Year Actual Average Headcount measure as follows −

Prior Year Actual Average Headcount:=CALCULATE( Finance Data [Actual Average Headcount], DATEADD( Date [Date], -1, YEAR))

Analyzing Data with Average Headcount Measures

Create a Power PivotTable as follows −

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

Add the measures Actual Average Headcount, Budget Average Headcount, Forecast Average Headcount, Prior Year Actual Average Headcount from Finance Data table to Values.

Insert a Spcer on the Fiscal Year field.

Select FY2016 in the Spcer.