Forum Discussion

theitguy's avatar
theitguy
Icon for Helper I rankHelper I
8 years ago

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

 

 

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

 

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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's avatar
      theitguy
      Icon for Helper I rankHelper I
      8 years ago

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