Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
28 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
46 |