Annual Average based on Distinct Count

[Jorgast]

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

[v-chuncz-msft · Accepted solution]

@Jorgast 

You may refer to the following posts.

• https://community.powerbi.com/t5/Desktop/Average-of-a-measure-for-each-value-of-the-years/m-p/312310...
• https://community.powerbi.com/t5/Desktop/Change-table-visualization-total/m-p/323290#M144103

[vanessafvg]

is this what you looking for?

[Jorgast]

@vanessafvg 

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. 

[vanessafvg]

ok unless im missing something, would this not work for you?  

avg of headcount 2 = AVERAGE(headount[Headcount])