locked
Weekly average of monthly data RRS feed

  • Question

  • Hi! I have a system where people are supposed to enter their power usage weekly. But some of them only enters monthly. In a report, I need the weekly average of the months usage.

    How do I create a calculated member to give me that average?
    This is what I have:
    WeekkWh
    1 0
    5 269853
    9 228402
    14 201596

    This is what I need:
    WeekkWh
    1 53970,6
    2 53970,6
    3 53970,6
    4 53970,6
    5 53970,6
    6 57100,5
    7 57100,5
    8 57100,5
    9 57100,5
    10 40319,2
    11 40319,2
    12 40319,2
    13 40319,2
    14 40319,2

    I've tried different forms of parameters to avg(), to no avail. To make things (perhaps) more complicated, if someone do their job right and enters their power usage weekly, the numbers shown in the report must be those, not an average for a month...

    I'm pretty new to MDX, so any help or pointers you have will be appreciated!

    • Edited by HansErik Lange Wednesday, January 27, 2010 5:00 PM Clarification
    Wednesday, January 27, 2010 4:56 PM

Answers

  • Hi,
    This sounds like something that would be good to fix in your ETL process.  Or, if you don't have any control over that, you could tweak your DSV so that monthly numbers are spread to a weekly average based on the number of weeks in the month.  This would make your calculations easier to understand in your cube.

    If you want to try MDX, you can create a calculated member that divides the sum of the months by the number of weeks, then use this calculation to find the weekly average.  This is an example doing a monthly average for the quarter in adventure works:

    WITH
      MEMBER [Measures].[Quarterly Total] AS
        Sum
        (
          Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Quarter]
          )
         ,[Measures].[Internet Sales Amount]
        )
      MEMBER [Measures].[Months in Quarter] AS
        Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Quarter]
        ).Children.Count
      MEMBER [Measures].[Monthly Average] AS
        [Measures].[Quarterly Total] / [Measures].[Months in Quarter]
    SELECT
      {
        [Measures].[Internet Sales Amount]
       ,[Measures].[Quarterly Total]
       ,[Measures].[Months in Quarter]
       ,[Measures].[Monthly Average]
      } ON 0
     ,{[Date].[Calendar].[Month]} ON 1
    FROM [Adventure Works]
    WHERE
      [Date].[Calendar Weeks].[Calendar Year].&[2003];
    • Proposed as answer by Raymond-Lee Thursday, January 28, 2010 7:39 AM
    • Marked as answer by Raymond-Lee Friday, February 5, 2010 2:57 AM
    Wednesday, January 27, 2010 5:59 PM
  • I'll give another example. Two users registers data. User1 register usage data every friday every week. User2, for some unknown reason, only register data the last friday in the month:
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 100 120 110 90 100 100 120 110
    User2       440       420

    If I used an average for the whole month, I'd get
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 105 105 105 105 107.5 107.5 107.5 107.5
    User2 110 110 110 110 105 105 105 105

    and not what I need:
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 100 120 110 90 100 100 120 110
    User2 110 110 110 110 105 105 105 105

    That's why I came to the conclusion that this is best done in the ETL, as you suggested.

      Hans Erik
    • Marked as answer by Raymond-Lee Friday, February 5, 2010 2:57 AM
    Monday, February 1, 2010 2:26 PM

All replies

  • Hi,
    This sounds like something that would be good to fix in your ETL process.  Or, if you don't have any control over that, you could tweak your DSV so that monthly numbers are spread to a weekly average based on the number of weeks in the month.  This would make your calculations easier to understand in your cube.

    If you want to try MDX, you can create a calculated member that divides the sum of the months by the number of weeks, then use this calculation to find the weekly average.  This is an example doing a monthly average for the quarter in adventure works:

    WITH
      MEMBER [Measures].[Quarterly Total] AS
        Sum
        (
          Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Quarter]
          )
         ,[Measures].[Internet Sales Amount]
        )
      MEMBER [Measures].[Months in Quarter] AS
        Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Quarter]
        ).Children.Count
      MEMBER [Measures].[Monthly Average] AS
        [Measures].[Quarterly Total] / [Measures].[Months in Quarter]
    SELECT
      {
        [Measures].[Internet Sales Amount]
       ,[Measures].[Quarterly Total]
       ,[Measures].[Months in Quarter]
       ,[Measures].[Monthly Average]
      } ON 0
     ,{[Date].[Calendar].[Month]} ON 1
    FROM [Adventure Works]
    WHERE
      [Date].[Calendar Weeks].[Calendar Year].&[2003];
    • Proposed as answer by Raymond-Lee Thursday, January 28, 2010 7:39 AM
    • Marked as answer by Raymond-Lee Friday, February 5, 2010 2:57 AM
    Wednesday, January 27, 2010 5:59 PM
  • Hi, thanks for your suggestions. I will try this! I'll have to create a Weeks In Month measure, but I think I can manage that :)
    Thursday, January 28, 2010 12:35 PM
  • Hi,
    One note on that, the sum is not needed in the calculation:

    WITH
      MEMBER [Measures].[Quarterly Total] AS
        Sum
        (
          Ancestor
          (
            [Date].[Calendar].CurrentMember
           ,[Date].[Calendar].[Calendar Quarter]
          )
         ,[Measures].[Internet Sales Amount]
        )
    It will perform better without it.
    Mark

    Thursday, January 28, 2010 2:31 PM
  • Hi,

    I was just finished generating the weeks per month measure when it occurred to me that doing a monthly sum and dividing by weeks won't work for the ones who reports weekly, as they would also get the average and not their real numbers.

    So I'm probably going for what you suggested first, do it in the ETL.
    Friday, January 29, 2010 4:58 PM
  • You should check that calculation out, it should work out the same for the weekly entries.  You are merely taking the sum of the month and dividing it by the count of the weeks.  This would be the same if you used the average function on it.  If I am understanding you right - that you wish to report a weekly average by month.

    Mark

    Friday, January 29, 2010 5:33 PM
  • I'll give another example. Two users registers data. User1 register usage data every friday every week. User2, for some unknown reason, only register data the last friday in the month:
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 100 120 110 90 100 100 120 110
    User2       440       420

    If I used an average for the whole month, I'd get
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 105 105 105 105 107.5 107.5 107.5 107.5
    User2 110 110 110 110 105 105 105 105

    and not what I need:
      Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8
    User1 100 120 110 90 100 100 120 110
    User2 110 110 110 110 105 105 105 105

    That's why I came to the conclusion that this is best done in the ETL, as you suggested.

      Hans Erik
    • Marked as answer by Raymond-Lee Friday, February 5, 2010 2:57 AM
    Monday, February 1, 2010 2:26 PM
  • Oh, I may have misunderstood.  I thought you were looking for weekly averages at the monthly level, but what you are really looking for is the monthly number to be spread weekly.  Makes sense to do that in the ETL.

    Good luck!
    Mark
    Monday, February 1, 2010 7:28 PM