Calculated expression in pivot doesn't give me totals

[#1]

I have created a pivot table with detail, ref as the lowest level. The problem is that my 'Price' field is a calculated expression which multiplies price % * Adjusted base. When I show detail in the pivot, the amount shows but no total. when i go a level up nothing shows. How can I show the total for calculated expression so even when I click on 2360 only and hide ref column, my detail will stay in place?

Thanks

[lironbaram]

hi have a look at the attach file

i used aggr functions

for price %  i used -

sum(aggr(([Price %]),ref,[Product Line ID]))

and for total price -

sum(aggr(sum([Adj Base])*(Sum (T12JuneYTD2014_DET)/SUM(TOTAL <[ref]> T12JuneYTD2014_DET) )*[Price %],ref,[Product Line ID]))

check it out

[PrashantSangle]

Hi,

Check attached file.

might help you

Regards,

[ecolomer]

Can you see my proposal?

[#5]

Hi Liron -

THIS IS IT!!!! It works!!! Thank you so much. Brilliant!!!

Thank you

[#6]

Enrique -

Yes this solution works. Thank you so much. I got it resolved.

Thank you,

Petra