Help with aggregating data properly

[Brysonds]

Hi!

Hoping I could get some help on something that is driving me crazy 🙂

Problem

I am doing some reporting on headcount, my data contains a column for "Number of Employees" -- this number is either 1 or 0 based on if the person is active or retired. 

When I sum this number up by month, the total is correct:

Jan = 500 <--- we had 500 employees active in January

Feb = 490

Mar = 510

etc...

When I sum this number up by quarter (using the date hierarchy), the total is incorrect:

Q1 = 1500

The same problem happens at the year level as well.

Obviously this is because it is set to sum. However, I can't use average because each record is a 1 or 0, so the average will always be around 1.

What I really need is an average of the sum of months for the quarter and year level. I don't mind splitting the visuals into multiple (one for year, one for quarter, one for month) if needed.

Any ideas?

[Greg_Deckler · Accepted solution]

Can you provide sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

However, generally to solve this kind of issue, you do something like:

Measure = AVERAGEX(SUMMARIZE('Table',[Month],"__Total",SUM([Column])),[__Total])

[Brysonds]

I am not entirely sure how this works, but after playing around, I used this formula (based on yours)

Measure = AVERAGEX(SUMMARIZE('Headcount', [Fiscal Year / Period].[Month], "Employee Total", SUM('Headcount'[Number of Employees (Column)])), [Number of Employees (Measure)])

The parts that are confusing above are:

1) The "Employee Total" <--- what is this doing?

2) I created a column and a measure with the same exact logic, why are both needed?