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-Power BI community,
I need your help. I am trying to look at the average number of people I had for 2019. So far, I was able to get the Distinct Count of their ID by day. When I compare the Averagex vs just doing a distinct count I get the same answer. I have also tried creating a summary table and used just the Date, State of Employment and ID but that gives me different daily numbers. I know I missing something in my measure but I'm not sure what it is.
Thanks in advanced
(Measure) Avg Headcount = averagex(TABLE, DISTINCTCOUNT(TABLE [ID])).
Calc Table = SUMMARIZE(TABLE, Dates[Date], Employee [State],
"Distinct", DISTINCTCOUNT(TABLE [ID]))
Desired Results
Monthly Average Headcount | |
Jan-19 | 338.615 |
Feb-19 | 323.625 |
Average both months | 331.42 |
Data Sample
DateHeadcount
2-Jan | 380 |
3-Jan | 384 |
4-Jan | 387 |
5-Jan | 70 |
7-Jan | 403 |
8-Jan | 411 |
9-Jan | 410 |
10-Jan | 390 |
11-Jan | 385 |
12-Jan | 89 |
14-Jan | 363 |
15-Jan | 372 |
16-Jan | 395 |
17-Jan | 387 |
18-Jan | 400 |
19-Jan | 59 |
21-Jan | 375 |
22-Jan | 395 |
23-Jan | 399 |
24-Jan | 390 |
25-Jan | 392 |
26-Jan | 49 |
28-Jan | 367 |
29-Jan | 375 |
30-Jan | 386 |
31-Jan | 391 |
1-Feb | 375 |
2-Feb | 55 |
4-Feb | 368 |
5-Feb | 402 |
6-Feb | 388 |
7-Feb | 381 |
8-Feb | 366 |
9-Feb | 47 |
11-Feb | 366 |
12-Feb | 396 |
13-Feb | 384 |
14-Feb | 354 |
15-Feb | 339 |
16-Feb | 46 |
18-Feb | 358 |
19-Feb | 412 |
20-Feb | 409 |
21-Feb | 361 |
22-Feb | 384 |
23-Feb | 49 |
25-Feb | 382 |
26-Feb | 392 |
27-Feb | 374 |
28-Feb | 379 |
Solved! Go to Solution.
You may refer to the following posts.
You may refer to the following posts.
is this what you looking for?
Proud to be a Super User!
Yes
Doing an average of a distinct count is not working for me. What i want to do is get the average count for the year.
ok unless im missing something, would this not work for you?
Proud to be a Super User!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |