how does calcuated measure rollup when the operator is divide ( a/b) RRS feed

  • Question

  • CREATE MEMBER CURRENTCUBE.[Measures].[Out of Stock Rate]
     AS IIF([Measures].[OOS] =0, null, [Measures].[Out of Stock Indicator]/[Measures].[OOS])


    When use that measure, how will it work when several records will be rollup? Will it work as

     AS IIF(sum([Measures].[OOS]) =0, null, sum([Measures].[Out of Stock Indicator])/sum([Measures].[OOS]))

    e.g, if we have 4 records as below, will the result for the total as (null+null+0+1)/(0+0+1+2)=1/3=33.33%?

    OOS ,Out of Stock Indicator

    0, null

    0, null



    Tuesday, August 9, 2011 8:07 AM


  • Your assumption is correct. Basically, the calculated measure is computed at runtime. Hence, whenever you refer the calculated measure, it checks out the value of the OSI and OOS at that level. So if you are referring to the OSR measure at the highest level, then it will sum up and show as 1/3.

    But if you are looking at a granular level, say for a product A which has OOS as 2 and OSI as 1, then the OSR would be 2/1=2.

    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Marked as answer by Daniel Wu Tuesday, August 9, 2011 8:38 AM
    Tuesday, August 9, 2011 8:36 AM