Power BI DAX - How to compute DISTINCTCOUNT then suming it with one formula?

Question

I have a table that contains many rows of people per month.

I want to create a measure that computes the mean number of people over a period.

For instance, my table could have 50 rows in January 23 (for only 30 distinct people), 40 rows in February (for 30 distinct people), and 100 rows in March 23 (for 90 distinct people):

If I select a period from January to March, I want a result of (30+30+90)/3 = 50. If I select February and March, I want a result of (30+90)/2 = 60.

I have tried two formulas:

CALCULATE(DISTINCTCOUNT([ID_PEOPLE]), ALLEXCEPT(REF_DATE, REF_DATE[MONTH]), PERIOD)
SUMX(REF_DATE[MONTH], CALCULATE(DISTINCTCOUNT([ID_PEOPLE]), PERIOD))

(PERIOD is a list of dates linked to my main table)

The result seems valid, but it is not entirely accurate.

Could you please help me?

Answer 1

First populate a calculated column using

Column = CALCULATE(DISTINCTCOUNT(Data[People]), ALLEXCEPT(Data, Data[Month]))

Then write dax for Table using Summarize dax function

Table = SUMMARIZE(Data, Data[Month], "People", MAX(Data[Column]))

Then write a measure to calculate Average

Measure = AVERAGE('Table'[People])