Forum Discussion
Helper IAverage of DistinctCount Per Day
Resident RockstarHi 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
Helper IHi 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()?