Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Average of users

 I want to make average of users per month.

Count of users are distinct each month.
But I want my average should look like: for the first two months (173+308)/2 or for three months (173+308+382)/3. 

The formula giving me  the avg. of all the distinct users selected in a given period.  

I tried something like this--

Average User Count by month = IF (
    HASONEVALUE ( 'Talend_GA sessions'[Month] );
    DISTINCTCOUNT ( 'public accounts_usersession'[user_id] );
   
DIVIDE(
       AVERAGEX('public accounts_usersession';DISTINCTCOUNT('public accounts_usersession'[user_id]));
       DISTINCTCOUNT('Talend_GA sessions'[Month])
)
)

 

Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    What about this one? :smileytongue:

     

    Average User Count by month =
IF (
    HASONEVALUE ( 'Talend_GA sessions'[Month] ),
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Talend_GA sessions'[Month] ),
            'Talend_GA sessions'[Month] <= SELECTEDVALUE ( 'Talend_GA sessions'[Month] )
        ),
        CALCULATE ( DISTINCTCOUNT ( 'public accounts_usersession'[user_id] ) )
    ),
    DIVIDE (
        AVERAGEX (
            ALL ( 'Talend_GA sessions'[Month] ),
            CALCULATE ( DISTINCTCOUNT ( 'public accounts_usersession'[user_id] ) )
        ),
        DISTINCTCOUNT ( 'Talend_GA sessions'[Month] )
    )
)

6 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

    Try this please

     

    Average User Count by month =
IF (
    HASONEVALUE ( 'Talend_GA sessions'[Month] ),
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Talend_GA sessions'[Month] ),
            'Talend_GA sessions'[Month] <= SELECTEDVALUE ( 'Talend_GA sessions'[Month] )
        ),
        CALCULATE ( DISTINCTCOUNT ( 'public accounts_usersession'[user_id] ) )
    ),
    DIVIDE (
        AVERAGEX (
            'public accounts_usersession',
            DISTINCTCOUNT ( 'public accounts_usersession'[user_id] )
        ),
        DISTINCTCOUNT ( 'Talend_GA sessions'[Month] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Zubair_Muhammad,

       

      Thank you for your solution. It's works great. But I have one more question why in the Total it showing the distinct count of whole year divided by 12.

       

       

      It should show me the sum of all the values here in the table divided by 12.

       

      Or, Am I missing something here ?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Anonymous

         

        Actually i didn't touch the second argument of IF.

         

         

        Check this one. I just added CALCULATE highlighted in red font below

         

        Average User Count by month =
IF (
    HASONEVALUE ( 'Talend_GA sessions'[Month] ),
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Talend_GA sessions'[Month] ),
            'Talend_GA sessions'[Month] <= SELECTEDVALUE ( 'Talend_GA sessions'[Month] )
        ),
        CALCULATE ( DISTINCTCOUNT ( 'public accounts_usersession'[user_id] ) )
    ),
    DIVIDE (
        AVERAGEX (
            'public accounts_usersession',
            CALCULATE ( DISTINCTCOUNT ( 'public accounts_usersession'[user_id] ) )
        ),
        DISTINCTCOUNT ( 'Talend_GA sessions'[Month] )
    )
)