Combine data from pivot table with table box and add a calculated field
Hi,
I was wondering if anyone would be able to help me.
1. I want to combine data from my pivot table and table box to form one big table.
2. I want to add 3 more columns to the end of this new table
a. Column 1 would be a calculated field called "difference" which will subtract the values in the current period from the prior period.
b. Column 2 will be a logical formula that will indicated whether a deal has been booked ect..
c. Column 3 will allow sales team to provide comments on specific deals.
Please see below sample data that will be used and the desired result.
Pivot Table
SUM (Probability %)
SUM (CNR($M)
SUM (AUM ($MM)
Period
Deal No.
Current
Prior
Current
Prior
Current
Prior
5232091
25
50
457884
362327
111451
121964
3894839
50
75
533442
323211
434566
435627
4927995
100
0
246711
923451
123456
378925
Table Box
AUM ($MM)
Deal No.
Region
Banker
Sales member
Current
Prior
Difference
748591
Canada
Tony
John
76
34
42
432040
Western
Hannah
Rachel
67
23
44
129041
Eastern
Rose
Temi
78
42
36
Desired result
AUM ($MM)
Review
Comments
Deal No.
Region
Banker
Sales member
Current
Prior
Difference
Booked deal
74859
Canada
Tony
John
76
34
42
AUM variance
43204
Western
Hannah
Rachel
67
23
44
CNR Variance
12904
Eastern
Rose
Temi
78
42
36
Booked deal
The end goal is to reproduce an existing report in an excel that will make it easier for the sales team to view their deals and to provide comments on the ones that are flagged up by the formula in Column 2. I welcome any suggestions on the best way to achieve this Please let me know if you require further clarification.
