Average headcount calculation

[maivarss]

Hi,

I currently have a large dataset that includes both headcount and financial data over a 12 month period. The heacount is reflected as a number by area and month, and I would like include a measure calculating the average headcount for the period in question (where it is possible to change the period for the average headcount calculation based on what period I'm chosing). For all financial data, the headcount column displays 0, and thus I fail to succeed using an average calculation (even after trying various methods to filter out the 0s). Help would be greatly appreciated!

Thanks,

Malin

[Greg_Deckler]

= CALCULATE(AVERAGE([Value]),[Value] > 0)

?

@maivarss

[maivarss]

Thanks smoupre, didn't specify my question clearly 🙂 I tried your suggestion before, and this only gives me the average by the district in the regions (which is quite low as there are very many districts), which then doesn't roll up to e.g. a global headcount average for the month for ALL the districts combined. So I suspect I need to somehow include a sum in the average formula - any thoughts?

[Greg_Deckler]

Can you give me some sample data and desired outcome?

[greggyb]

Do you want the average to be the total headcount divided by the number of regions, rather than the number of districts? AVERAGE() will return SUM( 'Table'[Field] ) / COUNT( 'Table'[Field] ). If you want a different denominator, just use a SUM() measure over an alternate count:

HeadCount =
SUM( FactHeadCound[HeadCountAmount] )

Regions =
DISTINCTCOUNT( FactHeadCount[Region] )

Average HeadCount across Regions =
DIVIDE( [HeadCount], [Regions] )

You can obviously do similar for other denominators.