Calculated field in Pivot help

[aravindhan_3]

Hi,
I have data like below

Colm A - Store Number
Colm B - Region
Colm C - This Week Sales
Colm D - Last Week Sales

I am creating a pivot table to get sales by Region and by Store so I dragged Region & Store in my row fields, Both TY & LY in value fields.

now i wanted the variance between this week and last week without adding a column in my raw data so i added a calcualted field called Variance (This week Sales - Last Week Sales) this is also working fine.
now i wanted the variance to be divided by Last week total sales. how do i do this in pivot? it this is not possible, can someone help me with the logic so that i can the raw data in such a way that i get this result in pivot

Regards
Arvind

[Paul_Hossler]

Can you create another Calculated field called


Variance% = (This week Sales - Last Week Sales) / Last Week Sales

[aravindhan_3]

this one will work, but i wanted like Variance% = (This week Sales - Last Week Sales) / toal( Last Week Sales ) this should be the value of Grand Total of last Week sales in pivot