5 Replies Latest reply on Mar 16, 2017 5:35 PM by Shinichiro Murakami

# Monthly Average of Aggregated Calculation

I have this workbook and I am trying to calculate DAU/MAU Ratio. It is just based on the number of days a particular user logged in the systems divided by 30 days.  However I wanted to get the Average for all users for every month. I am able to calculate it on a per user basis but my goal is to create a Monthly Bar Chart that is already the average of all the users login / 30 days.

On my attached workbook what I really wanted to arrive to be able to create the bar chart is to aggregate the average at monthly level.

Oct 2016 -   16.5%   (which is the average of  (20% and 13.3%)

Nov 2016     13.3%  ( which is the average of (10% and 16.6%)

Appreciate any help.

• ###### 1. Re: Monthly Average of Aggregated Calculation

Hi Raymond

I tried to solve through table calculation and I believe I can do that, but took time.

I changed approach and used LOD, which is much faster to solve.

[User Avg]

{fixed[Month], [User name]:sum([Number of Records])}/30

[Month Avg]

{fixed [Month]:avg([User Avg])}

Thanks,

Shin

• ###### 2. Re: Monthly Average of Aggregated Calculation

Thanks Shin. Worked like a charm.

• ###### 3. Re: Monthly Average of Aggregated Calculation

Thanks.

Shin

• ###### 4. Re: Monthly Average of Aggregated Calculation

Hello Shin

I apologize I realized that my data source has multiple records for each day per user. I used your suggested LOD calculation but because my  record is not 1:1 per day per user this particular LOD Calc does not give the correct percentage calculation..  {fixed[Month], [User name]:sum([Number of Records])}/30

I am attaching the workbook so you can see. My goal is to be able the Average of Day to Month Ratio per Month. (example for Oct 2016 --Avg. of 16.67% & 20%)

Appreciate further suggestion.

Ray

• ###### 5. Re: Monthly Average of Aggregated Calculation

Sorry for overlooking the question.

This might work?

[Month AVG SM]

{fixed [Month ID]:avg({fixed [User name],[Month ID]:countd([Date BK])}/30)}

Thanks,

Shin