Before Power Pivot, you could create calculated fields for pivot tables. This problem explores calculated fields versus
Question:
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
Step by Step Answer:
Business Analytics Data Analysis And Decision Making
ISBN: 9780357109953
7th Edition
Authors: S. Christian Albright, Wayne L. Winston