Average of DistinctCount Per Day

[theitguy]

Hello everyone,

I have a table Learning_Sessions:

Participant_ID	Launch_History_id	Launch_Date	Seconds_Spend
1	20984	17-11-2017	444
1	23271	17-11-2017	24
1	23273	19-11-2017	974
4	20970	23-11-2017	439
4	23301	23-11-2017	1
5	20935	05-11-2017	84
5	21819	05-11-2017	28
5	21817	17-11-2017	72
5	20983	17-11-2017	373
6	20981	29-11-2017	10
8	20987	03-11-2017	225
8	23378	17-11-2017	148
8	23386	17-11-2017	4
9	24764	05-12-2017	567

I would like to calculate the average of the DISTINCTCOUNT of ParticipantID per day.

In fact, I want two measures, 1) all days (days with and without sessions) and 2) only days with sessions.

The filter for the date period value would be a drilldown in a line chart for example. Actually, the average line of the line chart shows what I want as 2).

I had already a look at https://community.powerbi.com/t5/Desktop/Counts-per-day/td-p/185353, but there is just the sum calculated.

I tried the following, but it throws an error and it does not cover case 1).

Average_Users_Per_Day = 
Var MyTempTable = 
GROUPBY(Learning_Results_Single_Sessions;Learning_Results_Single_Sessions[Launch_Date];
"Users_Per_Day";
DISTINCTCOUNT(Learning_Results_Single_Sessions[Participant_ID])) 

RETURN AVERAGEX(MyTempTable;[Users_Per_Day])

[v-piga-msft]

Hi @theitguy,

You could try to modify your measure like below and it will be right.

Average_Users_Per_Day =
VAR MyTempTable =
    SUMMARIZE (
        Learning_Results_Single_Sessions,
        'Learning_Results_Single_Sessions'[Launch_Date],
        "Users_Per_Day", DISTINCTCOUNT ( 'Learning_Results_Single_Sessions'[Participant_ID] )
    )
RETURN
    AVERAGEX ( MyTempTable, [Users_Per_Day] )

In fact, I want two measures, 1) all days (days with and without sessions) and 2) only days with sessions.

---

In addition, I 'm a little confused about sessions, could you share a dummy pbix file which can reproduce the scenario and your expected output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[theitguy]

Hi Cherry @v-piga-msft,

thank you very much. I really need to understand the difference of SUMMARIZE AND GROUPBY.

---

Regarding your confusion, I think I can explain what I mean without sharing the .pbix because that might lead to more confusion:

Thanks to you, we are now covering case 2:

Launch_Date	Users_Per_Day
20-09-2017	19
21-09-2017	20
22-09-2017	4
23-09-2017	1
24-09-2017	2
25-09-2017	11
26-09-2017	6
27-09-2017	21
	Average 10,5

But, what if there are dates where no one has done something (case 1), which means that there are no records (single lines) for certain dates.

Launch_Date	Users_Per_Day
20-09-2017	19
21-09-2017	20
22-09-2017	4
23-09-2017
24-09-2017	2
25-09-2017	11
26-09-2017
27-09-2017	21
	Average 9,6

Can I measure those as well? Maybe by joining the sessions table temporarily with an extra table created with CALENDARAUTO()?