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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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