Hi there,
I'm trying to create a weighted allocation view in a pivot table based on combination of two set of values from the original table. Essentially I have a series of sales people, each belonging to a sales team, and that could be allocated to multiple sales region and to multiple products. I represented the allocation as a % of time spent (e.g., in the sample file attached, sales rep A is dedicated 90% to region NAR and 10% to EMEA, and spend 40% of his time selling Product A, and 60% of his time selling product B).
My goal is to produce a pivot that, for each sales team, shows what is the overall resources allocation by product for a given region. As in reality I have a lot more than 3 regions and 3 products, I want to do this calculation in the pivot rather than creating ad hoc columns in my original dataset. So for instance, based on values in the attached file, my aim get something like:
team EMEA resources Prod.A EMEA Prod.B EMEA Prod.C EMEA
BLU 1.5 1.1 0.3 0.1
GREEN 3.5 1.4 1 1.1
ORANGE 0.1 0.04 0.04 0.02
I was trying to get that through a calculated field (Prod.X EMEA = EMEA*Prod.X), but because the calculated field combines the sum of totals, rather than the individual lines, it skews all the values (see sheet 2 of attachment), because it sums all values instead of "zeroing" the rows where either EMEA or the product is equal to zero for a given sales rep.
Any suggestion on what I could use? As said, I dont want to create 9 extra fields on sheet1 with all the calculations, as in reality I'll be dealing with a much larger set of product and regions, so it's not a scalable option.
Thanks in advance!
Bookmarks