cancel
Showing results for
Did you mean:
Regular Visitor

## Help with aggregating data properly

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Help with aggregating data properly

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

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

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Help with aggregating data properly

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

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

Proud to be a Datanaut!

Regular Visitor

## Re: Help with aggregating data properly

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?