3 Replies Latest reply on Jul 27, 2018 10:27 PM by Deepak Rai

# LOD for monthly averages and grand total

Hi everyone,

My dataset includes daily information about sales (date & nr. of items & division) and we want to look at average sales in a month – but we do use weighted average as the picture below suggests – based on days.

Below is an illustration, how daily and monthly averages should be calculated.

There are two issues we are facing:

A) The total of the measure is not the total of the rows above, but pure arithmetic average from all the data. How to get the average of averages (see pic below)?

„Average Sales“ is now calculated as [Sales]/COUNTD([date]).

Numbers 1-6 represent weekdays (Mon-Sat)

Total average in this case should be 181 051/6 = 30 175

B) Averages also become plain arithmetic once I take the day dimension out of the table - such as here. I understand, its the LOD to be engaged, but have not figured out how to exactly do it. This table particularly shows values for april in 2016-2018…

I know these two are two sides of the same coin, so maybe one step will fix both (?) Any help is highly appreciated.

Thanks,

B.

• ###### 1. Re: LOD for monthly averages and grand total

Hi Barbora,

Pl attach some workbook. May be with Fake data to Help you.

Thanks

Deepak

• ###### 2. Re: LOD for monthly averages and grand total

Attached now.

Thank You,

B.

• ###### 3. Re: LOD for monthly averages and grand total

Hi Barbora,

If you Could Keep Your Cursor at Each Total a menu will come up, It is Set up to Automatic by Default, Change it to SUM, So you will get this:

For Your Second Sheet, Same thing and I also Wrote a Simple LOD to Give you Average for 2018 per Divison

As you can see, my Values Match with Yours

Thanks

Deepak

If it Helps, Pl mark It Helpful and CORRECT to Close Thread