Forum Discussion

maivarss's avatar
maivarss
Microsoft Employee
10 years ago

Average headcount calculation

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 me...
maivarss's avatar
maivarss
Microsoft Employee
10 years ago

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's avatar
Greg_Deckler
Community Champion
10 years ago
Can you give me some sample data and desired outcome?
  • greggyb's avatar
    greggyb
    Resident Rockstar
    10 years ago

    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.