locked
How to calculate Average on Rows RRS feed

  • Question

  •  

    I have Measure Normal Sell Price and  set the Measure properties AggregateFunction = AverageOfchildren and  when I  browse the cube using Excel 2010 I am getting Average on Grand Total on columns  correctly. But the grand total on Rows are not getting correctly it’s doing sum on Rows.

    Could anyone can advise how to get average calculated on Rows Measures.

    Is something I need to set properties on Cube solution or I need to create calculated Measure …?  Please advice

    I am attaching the screen shot.  If we see Grand Total on Rows is doing SUM which is not I want and Grand total on Columns is doing average it is ok.

    In above screen shot, i need to calculate average on Rows i.e. Avg on 12.49 and 16.66 but it’s doing Sum.. 

    Tuesday, March 13, 2012 1:55 AM

Answers

  • Hi Darren,

    Thank you for solution, it worked .

    • Marked as answer by srinivasR Tuesday, March 13, 2012 5:58 AM
    Tuesday, March 13, 2012 5:58 AM

All replies

  • Sorry, I think in Excel 2010, it's impossible. But if you want to use Report Builder, it's very easy.

    http://technet.microsoft.com/en-us/library/dd220460.aspx


    Regards, Nighting Liu

    Tuesday, March 13, 2012 2:23 AM
  • It's not impossible.

    The AverageOfChildren aggregation is part of the set of semi-additive aggregations that work differently across time, but simple sum across other dimensions (eg LastChild & LastNonEmpty). So it could also have been called AverageOfTimeChildren.

    To get an average that works across all dimensions you need to set your measure to use a SUM aggregation, then create a RowCount measure and finally create a calculated measure that is simple the sum divided by the count.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, March 13, 2012 2:30 AM
  • Hi darren

    Thank you for your reply.

    on what bases i should create rowcont measure. if possible could you please send me the code to create RowCount measure .  so that i can do rest of the calculation .

    advance thanks.

    Tuesday, March 13, 2012 3:15 AM
  • I would create this as a physical measure. (assuming that the count of rows in the fact table is the correct denominator to use for your average) Right click on the measure group that holds your Sell price measure and choose the "New Measure..." option and set the usage for this new measure to "Count of Rows"

    http://darren.gosbell.com - please mark correct answers

    Tuesday, March 13, 2012 4:17 AM
  • Hi Darren,

    Thank you for solution, it worked .

    • Marked as answer by srinivasR Tuesday, March 13, 2012 5:58 AM
    Tuesday, March 13, 2012 5:58 AM