cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theitguy Regular Visitor
Regular Visitor

Average of DistinctCount Per Day

Hello everyone,

 

I have a table Learning_Sessions:

Participant_IDLaunch_History_idLaunch_DateSeconds_Spend
12098417-11-2017444
12327117-11-201724
12327319-11-2017974
42097023-11-2017439
42330123-11-20171
52093505-11-201784
52181905-11-201728
52181717-11-201772
52098317-11-2017373
62098129-11-201710
82098703-11-2017225
82337817-11-2017148
82338617-11-20174
92476405-12-2017567

 

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).

 

average_users_per_day.png

 

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
Community Support Team
Community Support Team

Re: Average of DistinctCount Per Day

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.
theitguy Regular Visitor
Regular Visitor

Re: Average of DistinctCount Per Day

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_DateUsers_Per_Day
20-09-201719
21-09-201720
22-09-20174
23-09-20171
24-09-20172
25-09-201711
26-09-20176
27-09-201721
  
  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_DateUsers_Per_Day
20-09-201719
21-09-201720
22-09-20174
23-09-2017 
24-09-20172
25-09-201711
26-09-2017 
27-09-201721
  
  Average 9,6

 

 

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