Create calculated field by combining original row values, not on field in pivot

[CarManI]

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!

[JeteMc]

Hello CarManI and Welcome to Excel Forum.
As you have the 2016 version of Excel the following may be helpful.
Using Power Query (Get & Transform) two tables are produced, tbl_Percent_by_Region and tbl_Percent_by_Product.
Again using Power Query the two tables are merged using both the Sales Rep and Sales Team columns as matching.
Once the tbl_All_Data table is produced a new column is added that multiplies the Region percent and Product percent using: =[@[Reg. Percent]]*[@[Prod. Percent]]
From tbl_All_Data a pivot table is produced with the Sales Teams and Regions in the Row field, the Products in the column field and the Reg. x Prod. Percent in the values field.
In the attached file the filter for the Region field is set to display only EMEA, however it could show any/all of the other regions as well.
Let us know if you have any questions.