2 Replies Latest reply: Jul 31, 2014 12:00 PM by gdeepak RSS

    Average for a quarter and sum in grand total

    gdeepak

      Hi All,

       

      I have a report (pivot table) in which I display the sales information. I have category, sub category in rows. Year, Quarter in columns. Sales are loaded at the day level in the fact table. My report should show quarterly average of the sales. i.e all the sales in a month should be summed up and, sales in 3 months should be averaged to get average quarterly sales.  We have to show the sub total of sales for each category. We also need to show % of Sales for each subcategory with in a category.

       

      I did the following,

      1. The aggregation of Sales column in RPD is at sum

      2. In the report, I've pulled month in the criteria and excluded that from the pivot view

      3. I changed the aggregation (for totals rum) to SUM in criteria->column formula

      4. I changed the aggregation to AVG in pivot view measures

      5. I duplicated the measure to show percent of Sales

      6. I enabled total in the rows.

       

      Everything works fine except the value of the sub total for sales which is giving me average of the subcategories at the category level, instead it should be a sum.  The % in the total is correct where it is giving 100% for each category

       

      Please help me to achieve the sum at the sub total level

       

      Thanks!

      Regards

      Deepak