Forum Discussion

10 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Anonymous

     

    A rolling 3 month average or a hardcoded measure to give you the average to those specific days?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Phil_Seamark yes, just the average of those 3 numbers. The numbers are already the way I need them to be, I just need to average those 3. I've tried a AVERAGEX function with it which gets me really close, but for some reason the function changes the numbers slightly for certain months, making the overall average incorrect:

       

      The Month End column comes from my date table, my retention and last quarter retention is coming from my fact table. So with the above formula, the only thing I was trying to accomplish was averaging the 3 highlighted values in my [retention] column. So (87.08+87.59+87.46)/3 which should give 87.38. Instead, it is returning (87.03+87.54+87.54)/3 which gives the 87.36. I'm not sure why it is changing my original [retention] values. I know it is a very very small difference, but boss man only wants to accept perfect...

      • dedelman_clng's avatar
        dedelman_clng
        Community Champion
        8 years ago

        Anonymous -

         

        Be aware that mathematically, averaging values that are already ratios themselves is not correct, unless all of the ratio's denominators are exactly the same.  For example

         

        Q1 - 25/100 = .25

        Q2 - 20/200 = .10

        Q3 - 50/100 = .50

         

        (.25 + .10 + .50) / 3 = .2833

         

        but Average(Q1 thru Q3) = (25+20+50) / (100+200+100) = .2375

         

        So this may be the discrepancy in the two numbers - try recalculating Last Quarter Retention by using the same formula with which you calculate the monthly retention, but sum up the numerators and denominators before dividing.

         

        Hope this helps

        David