cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brysonds Regular Visitor
Regular Visitor

Help with aggregating data properly

Hi!

 

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

 

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
Super User

Re: Help with aggregating data properly

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])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: Help with aggregating data properly

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])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Brysonds Regular Visitor
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?