Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Average of users

Screenshot_30.png 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])
)
)

 

1 ACCEPTED SOLUTION

@Anonymous

 

What about this one? Smiley Tongue

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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.

Screenshot_31.png

 

 

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

 

Or, Am I missing something here ?

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Now, I am getting some strange value in the total. Screenshot_32.png

 

 

@Anonymous

 

What about this one? Smiley Tongue

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Now, It's still something strange. But no worries I can live with that. Thanks a lot for your help.

Screenshot_33.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.