cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Average of DistinctCount Per Day

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])`

2 REPLIES 2
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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()?