Sum of rows per ID and then Avg of All

[dolevh]

Hi All, 

I have this table : 

ID	StartDate	EndDate	Price	CountofNum
123	1/1/21	1/31/21		9999
123	2/1/21	2/28/21	10	1
123	3/1/21	3/31/21	10	0
123	4/1/21	4/30/21	10	0
123	5/1/21	5/16/21		9999
12345	1/1/21	1/31/21		9999
12345	2/1/21	2/28/21		9999
12345	3/1/21	3/31/21	20	1
12345	4/1/21	4/30/21	20	0
12345	5/1/21	5/31/21	20	0
12345	6/1/21	6/30/21	20	0
123456789	1/1/21	1/31/21		1
123456789	2/1/21	2/14/21		9999

I'm looking for dax that shows me the average of all rows that the value in the Table[CountofNum] column is '1' or '0' per ID.

For example: 

ID	SumOfData
123	1 + 0 + 0 = 3
12345	1 + 0 + 0 + 0 = 4
123456789	1 = 1

and then, Table[SumOfData] / Table[ID]
(3 + 4 + 1) / 3 
'3' is beacuse the ID unique.

thanks!

[v-easonf-msft]

Hi, @dolevh 

Referring to @Anonymous ‘s solution, I made a sample for reference.

If your problem has been solved, please accept a helpful reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

Try This

averageid=

var nbid = calculate(DISTINCTCOUNT(Feuil10[ID]),Feuil10[  CountofNum] in {0,1})

var nb01 = calculate(COUNT(Feuil10[  CountofNum]),Feuil10[  CountofNum] in {0,1})

var result= 

divide([nb01],[nbid],0)

return 

result

[Greg_Deckler]

@dolevh You can use AVERAGEX and FILTER