Trying to get a row sub total from a pivot table to calculate percentages

[ksmccann]

Hi All

     I am trying to get the total from one of my sub totals and carry it through to all rows in my pivot table so I can do a calculation that will show the subsequent rows as percentage of the very first sub total.

       I have a pivot table that shows Category, product and dates. The Category and Products are the rows and dates are the columns. Lets say on 1/1/2011, I have 2 products that make up my first category and the total of these 2 products sales is $200. For every row after that I want to divide the product sales for that day by the $200 to see what percenatge that represents.

     I have tried using the AGGR funciton to get the value but when applied in the pivot table it seems to only execute on the first row in the first group and after that everything else displays as zeroes. I have tried set analysis to apply a filter that is specific to the order category, again this only displays on one of the rows and the rest display as zero. I need to carry this number forward on all rows.

     Here are some of the expressions I have tried so far

(Aggr(sum( if (upper(Category)= upper('Original Order') ,  OrderTotal)),   OrderDate))

sum( {<RowNo = 1 >} OrderTotal )

=(Aggr(sum( {<[Category]= {'Original Order'} >} OrderTotal ),   OrderDate))

     I considered using variables but the number of dates is variable so I don't think this would work. I considered using 2 tables but withy filtering I can't pre-aggregate the values and have them join back to the details. I also considered having 2 pivot tables but again don't see how I can reference mutliple sets of totals (one per day).

    I have attached the QV dashboard I have with some dummy data as well as the excel data. The second measure column is the one that I cannot get to carry the value forward.

What I need to get is the second measure column to repeat the first group sub total on EVERY line. So for 7/16/2011 the value of 99,635.55 should appear on all lines. Right now it is only appearing on the very first line. If I can get this I can calculate my percentages

                Thanks for your help

[jonathandienst · Accepted solution]

Hi

I don't think that you cane get what you want with that data set. What I suggest that you load a data island with the product and category and use this island as the dimension. See if the updated file I have attached is what you are after.

Hope that helps

Jonathan

[ksmccann]

Jonathan,

This is exactly what I needed. Thanks for your help.

Thanks

Kevin