Average number of users across different sites

[Anonymous]

Hello,

I'm trying to create a column or measure that will show the average number of users across 3 different websites. Currently, my data looks like this:

Site	Date	User Identifier
random.com	01/05/2021	582
random.com	01/05/2021	583
random.com	01/05/2021	584
random.com	02/05/2021	586
cats.com	03/05/2021	587
cats.com	04/05/2021	588
dogs.com	05/05/2021	589
dogs.com	05/05/2021	590

My current measure is

Average users = DIVIDE(DISTINCTCOUNT(Table[User Identifier]),CALCULATE(DISTINCTCOUNT(Table[Date]), ALL(Table)))

This measure actually works but because not all three sites have been rolled out on the same day, if I filter by one of the sites that rolled out later in the year, I get a daily average user number lower than 1, which is technically not possible.  My initial instinct was to create a number of days column using

Number of Days = RANKX(Table, Table[Date],,ASC,Dense)

This creates a new column with numbers as days. So, for the above example, the first three rows would be 1, followed by 2,3,4 and the last two rows would be 5. But I cannot filter this calculation with different sites as RANKX doesn't allow using filters.

Any ideas, how can I overcome this?

Thanks,

[amitchandak · Accepted solution]

@Anonymous , Try a new measure like

averageX(summarize(Table, Table[Site], Table[Date], "_1",DISTINCTCOUNT(Table[User Identifier])),[_1])