none
Calculate average daily value RRS feed

  • Question

  • A have Fact table (simplified)

    ordNo | DateID   | Value
    1       20100101    10
    2       20100101    20
    3       20100102    30
    4       20100202   100
    5       20100202   150

    I have to calculate "day average value" member, so when I look data for every day I get:

    DateID  | AvgValue
    20100101  15
    20100102  30
    20100201  125

    (I calculate sum-value/count)

    buth when I look on montly level, average should be based on daily averages (so I get "average day value" inside month):

    Month  | AvgValue
    201001   22,5
    201002   125

    And againt when I look on year level, the value should also be calculated from daily averages like:

    Year   | 56,6

    (so 56,6 is average daily value)

    Can somebody help me with this calculated-member expression?

    Thanks in advance.

    Friday, May 28, 2010 7:55 AM

Answers

  • Okshilato, i dont think that will do what he needs as he requires the average to be calculated at the date level before aggregating.  One possibility I thought about would be using the AverageOfChildren aggregation but I think this will only work if the leaf level are dates -- and in his fact data there are multiple entries for each date;

    Perhaps configuring a [day average] which will do the average of child members (in effect, first getting the daily average) and then use that calculated measure as the basis for the a standard AverageOfChildern aggregation of the Time dimension?


    Javier Guillen
    Friday, May 28, 2010 3:53 PM
  • Hi Matej,

    The best solution would be to add an additional fact-tabel to the cube with the aggregates per day, excluding the OrdNo's. The fact-table should have 1 record for each day having the avg value from the ordNo's of that day, like your example:

    DateID  | AvgValue
    20100101  15
    20100102  30
    20100201  125
    This allows you to take the avg(avgvalue) for all levels above (week, month, quarter, year) without the needo setup (too) complex MDX to achieve this. Would that be possible ?

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, May 28, 2010 8:50 AM

All replies

  • Hi Matej,

    The best solution would be to add an additional fact-tabel to the cube with the aggregates per day, excluding the OrdNo's. The fact-table should have 1 record for each day having the avg value from the ordNo's of that day, like your example:

    DateID  | AvgValue
    20100101  15
    20100102  30
    20100201  125
    This allows you to take the avg(avgvalue) for all levels above (week, month, quarter, year) without the needo setup (too) complex MDX to achieve this. Would that be possible ?

    hth,

    Cees

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, May 28, 2010 8:50 AM
  • This solution doesn't work for me (or I just don't understand "everything"). Problem is that I have more "rich" fact table (with  more dimensions for example).

    ordNo | DateID  | Value | CustomerID | ...
    1    20100101  10     1 
    2    20100101  20     2
    3    20100102  30     3
    4    20100202  100    1
    5    20100202  150    2

    So I cannot "precalculate" average day value, becouse I don't know for example which fillters wil user apply to cube. So I belive that daily average should be calculated on the cube, but I don't know how :(

    Thanks for any advance help...

    Friday, May 28, 2010 11:19 AM
  • If you are not used to work with time hierarchies when calculated AVG’s, here an easy way to do it, no matter what dimension you will use:

     

    Assumptions: measure name to AVG is “Value”

     

    In Cube Structure section add 2 measures from the same column with different aggregation types and create:

    1) “Sum Value” from Value column having Sum aggregation function;

    2) “Count Value” from Value column having count aggregation function;

     

    In cube Calculations section create a measure Called “AVG Value” with the following formula:

     

    IIF([Measures].[ Sum Value]=0,0,[Measures].[ Sum Value]/[Measures].[ Count Value])

     

    Don’t forget to setup the “Format String” to “Percent”

     

    Hope this help

    Oks

    Friday, May 28, 2010 3:37 PM
  • Okshilato, i dont think that will do what he needs as he requires the average to be calculated at the date level before aggregating.  One possibility I thought about would be using the AverageOfChildren aggregation but I think this will only work if the leaf level are dates -- and in his fact data there are multiple entries for each date;

    Perhaps configuring a [day average] which will do the average of child members (in effect, first getting the daily average) and then use that calculated measure as the basis for the a standard AverageOfChildern aggregation of the Time dimension?


    Javier Guillen
    Friday, May 28, 2010 3:53 PM
  • Okshilato, i dont think that will do what he needs as he requires the average to be calculated at the date level before aggregating.  One possibility I thought about would be using the AverageOfChildren aggregation but I think this will only work if the leaf level are dates -- and in his fact data there are multiple entries for each date;

    Perhaps configuring a [day average] which will do the average of child members (in effect, first getting the daily average) and then use that calculated measure as the basis for the a standard AverageOfChildern aggregation of the Time dimension?


    Javier Guillen

    I belive you're right....my bad
    Friday, May 28, 2010 5:47 PM