Before Power Pivot, you could create “calculated fields” for pivot tables. This problem explores calculated fields versus Power Pivot’s measures for ratios. The P04_21.xlsx file contains a list of transactions for a specific product at various stores for the last several months. Each transaction lists the revenue from the transaction and the number of units of the product sold in the transaction. The ratio of these, also listed, is the unit price at the time the product was sold:

 

Transaction	Store	Units Sold	Revenue	Unit Price
1	Greenville	7	$325.78	$46.54
2	Spartenburg	8	$359.92	$44.99
3	Charleston	9	$468.18	$52.02
4	Orangeburg	6	$269.22	$44.87
5	Greenville	7	$338.10	$48.30
6	Orangeburg	8	$357.20	$44.65
7	Columbia	7	$381.15	$54.45
8	Charleston	9	$456.21	$50.69
9	Greenville	3	$138.69	$46.23
10	Spartenburg	8	$373.84	$46.73
11	Orangeburg	4	$179.36	$44.84
12	Charleston	7	$365.89	$52.27
13	Greenville	10	$475.00	$47.50
14	Orangeburg	4	$177.60	$44.40
15	Spartenburg	8	$365.20	$45.65
16	Spartenburg	1	$46.29	$46.29
17	Columbia	10	$543.60	$54.36
18	Greenville	4	$191.08	$47.77
19	Orangeburg	4	$179.64	$44.91
20	Charleston	5	$267.50	$53.50
21	Orangeburg	10	$439.60	$43.96
22	Greenville	2	$96.50	$48.25
23	Greenville	6	$278.34	$46.39
24	Greenville	2	$92.58	$46.29
25	Greenville	1	$47.97	$47.97
26	Orangeburg	1	$46.55	$46.55
27	Charleston	3	$150.72	$50.24
28	Orangeburg	10	$445.80	$44.58
29	Columbia	9	$478.71	$53.19
30	Columbia	6	$326.46	$54.41
31	Charleston	9	$489.96	$54.44
32	Orangeburg	10	$469.20	$46.92
33	Orangeburg	1	$46.58	$46.58
34	Spartenburg	5	$232.20	$46.44
35	Spartenburg	7	$315.07	$45.01
36	Columbia	2	$106.44	$53.22
37	Spartenburg	6	$282.36	$47.06
38	Greenville	3	$142.44	$47.48
39	Spartenburg	4	$185.24	$46.31
40	Orangeburg	9	$398.88	$44.32
41	Orangeburg	4	$184.76	$46.19
42	Spartenburg	6	$274.32	$45.72
43	Orangeburg	3	$135.27	$45.09
44	Columbia	9	$480.96	$53.44
45	Orangeburg	3	$137.43	$45.81
46	Charleston	3	$162.06	$54.02
47	Orangeburg	4	$180.96	$45.24
48	Spartenburg	7	$325.43	$46.49
49	Spartenburg	10	$446.40	$44.64
50	Columbia	7	$382.41	$54.63
51	Spartenburg	7	$311.08	$44.44
52	Orangeburg	9	$410.67	$45.63
53	Greenville	6	$285.06	$47.51
54	Orangeburg	4	$181.20	$45.30
55	Columbia	9	$469.17	$52.13
56	Charleston	1	$52.89	$52.89
57	Columbia	10	$555.60	$55.56
58	Orangeburg	10	$450.60	$45.06
59	Charleston	7	$373.03	$53.29
60	Charleston	2	$105.06	$52.53
61	Columbia	4	$211.76	$52.94
62	Charleston	6	$320.22	$53.37
63	Greenville	10	$467.00	$46.70
64	Greenville	7	$342.23	$48.89
65	Spartenburg	9	$403.20	$44.80
66	Orangeburg	9	$416.07	$46.23
67	Charleston	1	$51.69	$51.69
68	Greenville	10	$476.00	$47.60
69	Columbia	4	$214.40	$53.60
70	Greenville	8	$364.80	$45.60
71	Orangeburg	6	$271.44	$45.24
72	Spartenburg	5	$226.40	$45.28
73	Greenville	3	$141.69	$47.23
74	Charleston	3	$155.55	$51.85
75	Spartenburg	7	$334.25	$47.75
76	Spartenburg	5	$232.75	$46.55
77	Greenville	6	$284.40	$47.40
78	Charleston	4	$209.76	$52.44
79	Charleston	7	$364.28	$52.04
80	Greenville	9	$414.99	$46.11
81	Columbia	5	$260.80	$52.16
82	Orangeburg	8	$372.72	$46.59
83	Columbia	2	$108.00	$54.00
84	Columbia	10	$535.00	$53.50
85	Greenville	1	$47.22	$47.22
86	Orangeburg	9	$404.28	$44.92
87	Orangeburg	2	$90.48	$45.24
88	Charleston	8	$419.36	$52.42
89	Greenville	7	$326.41	$46.63
90	Orangeburg	7	$318.64	$45.52
91	Orangeburg	10	$458.20	$45.82
92	Greenville	7	$334.18	$47.74
93	Charleston	6	$319.32	$53.22
94	Charleston	9	$469.17	$52.13
95	Charleston	2	$105.42	$52.71
96	Spartenburg	9	$414.99	$46.11
97	Orangeburg	3	$135.84	$45.28
98	Greenville	8	$381.36	$47.67
99	Orangeburg	7	$310.24	$44.32
100	Columbia	4	$216.76	$54.19
101	Greenville	9	$429.48	$47.72
102	Columbia	2	$108.96	$54.48
103	Greenville	9	$423.63	$47.07
104	Columbia	2	$106.84	$53.42
105	Columbia	5	$272.20	$54.44
106	Columbia	8	$412.72	$51.59
107	Charleston	7	$380.66	$54.38
108	Orangeburg	5	$224.20	$44.84
109	Orangeburg	2	$90.82	$45.41
110	Orangeburg	6	$274.44	$45.74
111	Greenville	1	$47.63	$47.63
112	Orangeburg	8	$364.16	$45.52
113	Orangeburg	7	$329.21	$47.03
114	Greenville	9	$431.55	$47.95
115	Columbia	9	$469.08	$52.12
116	Charleston	2	$102.70	$51.35
117	Orangeburg	9	$404.10	$44.90
118	Orangeburg	2	$92.30	$46.15
119	Orangeburg	8	$364.72	$45.59
120	Greenville	7	$336.14	$48.02
121	Columbia	8	$439.04	$54.88
122	Spartenburg	6	$267.12	$44.52
123	Greenville	1	$48.02	$48.02
124	Greenville	4	$186.68	$46.67
125	Columbia	10	$530.40	$53.04

a. Create a pivot table but do not add it to a Data Model. Drag Unit Price to the Values area and Store to the Rows area. Are the values in the pivot table meaningful? Does it help if you summarize by Average rather than Sum? Explain.

b. With this same pivot table select