Sum of Unique Values and Average of Unique Values - Not total unique users

[NiugeS]

Hi,

I have a table where I have several thousands of bookings with a columns for the start date the booking and by what user.  I have created a table with the date field (date hiearchy)  and also the user field such as below.  If I put the total on, it gives me a count of all unique users for the period. 

As I am looking to report the monthly average of the below, I need to work out the sum of the total for each month (4189) and then divide this by the amount of months, in this case 12 giving me 349.08.  I will be importing reports with different date periods.  I've seen a few examples but getting stuck ....

Year	Month	Count of Unique Users
2018	January	258
2018	February	457
2018	March	565
2018	April	152
2018	May	215
2018	June	321
2018	July	154
2018	August	568
2018	September	361
2018	October	859
2018	November	45
2018	December	234

Any guidance or assistance appreciated.

[v-xiaotang]

Hi @NiugeS 
Have you solved your problem? If yes, kindly mark the answer helpful as the solution, so that the others having same question can find this post quickly.
Otherwise, please provide more details about your problem so that we can work on it further.
Thanks.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @NiugeS 

Add a column with the year/month something similar to:

YearMonth = FORMAT(Table[Start Date], "yyyymm)

Now do the following measure:

Average = divide(DistinctCount(Table[UserID), DistinctCount(Table[YearMonth]))

should give expected result, but this depends also on the context.

[NiugeS]

@MFelix Hi Miguel - apologies for the delayed response and thank you for the assistance.  I have been trying your suggestion but I do not believe it is providing the desired result.  It is providing an average of unique users accros the entire period.

In each month I could have (for example) 50 unique users.  If these same users were to book each month, then the equation would result in 50 unique users / 12.

What I am looking for is each month's unique users to be added together to work out a monthly unique user count.  Example, 50 unique users who book every month would be 600/12 = 50.

Hope that makes sense.   

I am looking at something slighly different.