PDA

View Full Version : [SOLVED] Calculated field in Pivot help



aravindhan_3
08-03-2015, 02:07 AM
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
08-03-2015, 08:03 AM
Can you create another Calculated field called


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

aravindhan_3
08-03-2015, 08:30 AM
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