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

    LOD for monthly averages and grand total

    Barbora Spacilova

      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.

      Screen Shot 2018-07-27 at 17.27.16.png

       

       

      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)

      Screen Shot 2018-07-27 at 16.40.08.png

      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…

       

      Screen Shot 2018-07-27 at 17.16.24.png

      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.