Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |