Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]) ) )
Solved! Go to Solution.
@Anonymous
What about this one?
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] ) ) )
@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] ) ) )
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 ?
@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] ) ) )
Now, I am getting some strange value in the total.
@Anonymous
What about this one?
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] ) ) )
Now, It's still something strange. But no worries I can live with that. Thanks a lot for your help.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |