Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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])
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])
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?