Analytics Average

[nkpriya]

Hi

I have sample data for 5 employees,

1st col -week ending,2nd col- Manager 1, 3rd col - Manager 2, Emp col has emp name.

In Ctgy we have Run, Build and last col is calculated the % of Run and Build for each employee.

Now, when I run pivot for each week, Average of Build, Run calculated as 49.2% and Build calculated as 67.1%, however if we sum both, it has to be 100%.
Know that excel calculating average % based on # of rows. However, I need % to display as 67.17% and 32.82% which makes it total of 100%. Is there a way to do that? Manual way is add missing Run, build rows for each employee with 0%. But I have data like millions ofrows which I cannot do that. Please advise. Thanks

Week Ending SGTM RM Emplo Type Emp Count Ctgy Actuals Actuals2
Apr 10, 2015 S1 M1 Employee A1 1 Build 21 36.84%
Apr 10, 2015 S1 M1 Employee A1 0 Run 36 63.16%
Apr 10, 2015 S1 M2 Employee A2 1 Build 40 100.00%
Apr 10, 2015 S1 M2 Employee A3 1 Build 30 75.00%
Apr 10, 2015 S1 M2 Employee A3 0 Run 10 25.00%
Apr 10, 2015 S1 M2 Employee A4 1 Build 15.5 33.70%
Apr 10, 2015 S1 M2 Employee A4 0 Run 30.5 66.30%
Apr 10, 2015 S1 M2 Employee A5 1 Build 23 57.50%
Apr 10, 2015 S1 M2 Employee A5 0 Run 17 42.50%

Thanks

[Mandy11]

Attach a spreadsheet

[popipipo]

Try this solution.

[oeldere]

With an pivot table.

See the attached file.