Explain Why this Pivot Table is Layed Out this Way

[bencar]

Hi guys,

Trying to figure out why this pivot table is layed out the way it is. Here are my 4 questions. Below that is the exercise and its info.

1. Why is 'design' in the column area?
2. Why are 'amusement park' and 'roller coaster' in the row area?
3. Why is 'type' in the filter section?
4. In creating pivot tables in the future where should I put data into which PT data fields??


Heres the exercise. And below is all the info:

Convert this data into a pivot table and find the overall average speed of all rides that satisfy the following criteria:

• The Type is Steel.

• The Design is Sit Down.

• The Amusement Park has the word adventure somewhere in the title.

Roller Coaster	Amusement Park	Type	Design	Status	Opened	Speed
Air	Alton Towers	Steel	Flying	Operating	2002	46.6
Boomerang	Pleasure Island Family Theme Park	Steel	Sit Down	Operating	1993	47
Cobra	Paultons Park	Steel	Sit Down	Operating	2006	31.1
Colossus	Thorpe Park	Steel	Sit Down	Operating	2002	45
Corkscrew	Alton Towers	Steel	Sit Down	Operating	1980	40
Corkscrew	Flamingo Land Theme Park & Zoo	Steel	Sit Down	Operating	1983	40
Crazy Mouse	South Pier	Steel	Sit Down	Operating	1998	29.1
Crazy Mouse	Brighton Pier	Steel	Sit Down	Operating	2000	29.1
Enigma	Pleasurewood Hills	Steel	Sit Down	Operating	1995	34
Express	M&Ds Scotland's Theme Park	Steel	Sit Down	Operating	2006	28
Fantasy Mouse	Fantasy Island	Steel	Sit Down	Operating	2000	29.1
G Force	Drayton Manor Park	Steel	Sit Down	Operating	2005	43.5
Grand National	Pleasure Beach, Blackpool	Wood	Sit Down	Operating	1935	40
Infusion	Pleasure Beach, Blackpool	Steel	Inverted	Operating	2007	49.7
Irn-Bru Revolution	Pleasure Beach, Blackpool	Steel	Sit Down	Operating	1979	45
Jubilee Odyssey	Fantasy Island	Steel	Inverted	Operating	2002	63
Jungle Coaster	Legoland Windsor	Steel	Sit Down	Operating	2004	35
Knightmare	Camelot Theme Park	Steel	Sit Down	Operating	2007	43.5
Kumali	Flamingo Land Theme Park & Zoo	Steel	Inverted	Operating	2006	54.9
Magic Mouse	Brean Leisure Park	Steel	Sit Down	Operating	2007	29.1
Megafobia	Oakwood Theme Park	Wood	Sit Down	Operating	1996	48
Millennium Roller Coaster	Fantasy Island	Steel	Sit Down	Operating	1999	55.9
Nemesis	Alton Towers	Steel	Inverted	Operating	1994	50
Nemesis Inferno	Thorpe Park	Steel	Inverted	Operating	2003	47.8
New Roller Coaster	New MetroLand	Steel	Sit Down	Operating	1988	26.8
Oblivion	Alton Towers	Steel	Sit Down	Operating	1998	68
Pepsi Max Big One	Pleasure Beach, Blackpool	Steel	Sit Down	Operating	1994	74
Rage	Adventure Island	Steel	Sit Down	Operating	2007	43.5
Rat	Loudoun Castle	Steel	Sit Down	Operating	2005	28
Rattlesnake	Chessington World of Adventures	Steel	Sit Down	Operating	1998	28
Rhino Coaster	West Midlands Safari Park	Steel	Sit Down	Operating	1992	28.5
Rita - Queen of Speed	Alton Towers	Steel	Sit Down	Operating	2005	61.1
Roller Coaster	Great Yarmouth Pleasure Beach	Wood	Sit Down	Operating	1932	45
Roller Coaster	Pleasure Beach, Blackpool	Wood	Sit Down	Operating	1933	35
Roller Coaster	Wicksteed Park	Steel	Sit Down	Operating	2000	28
Shockwave	Drayton Manor Park	Steel	Stand Up	Operating	1994	53
Speed: No Limits	Oakwood Theme Park	Steel	Sit Down	Operating	2006	59
Stealth	Thorpe Park	Steel	Sit Down	Operating	2006	80
Tornado	M&Ds Scotland's Theme Park	Steel	Sit Down	Operating	1998	44.7
Tsunami	M&Ds Scotland's Theme Park	Steel	Inverted	Operating	2004	38
Twist and Shout	Loudoun Castle	Steel	Sit Down	Operating	2003	41
Twister	Lightwater Valley	Steel	Sit Down	Operating	2001	29.1
Ultimate	Lightwater Valley	Steel	Sit Down	Operating	1991	50
Vampire	Chessington World of Adventures	Steel	Suspended	Operating	2002	45
Velocity	Flamingo Land Theme Park & Zoo	Steel	Sit Down	Operating	2005	54
Wall's Twister Ride	West Midlands Safari Park	Steel	Sit Down	Operating	1998	29.1
Whirlwind	Camelot Theme Park	Steel	Sit Down	Operating	2003	37.3
Wild Mouse	Flamingo Land Theme Park & Zoo	Steel	Sit Down	Operating	1997	28
Wipeout	Pleasurewood Hills	Steel	Sit Down	Operating	2007	47
X:\ No Way Out	Thorpe Park	Steel	Sit Down	Operating	1996	27.7

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[gmhumphr]

I'm going to skip your enumerated questions, because there isn't a definitive answer for the right way to approach a pivot table. It comes down to more of a style question. Instead, I'll talk about how I think about pivot tables when I create them.

From the problem you are presented with (or the question you are trying to answer), work from the bottom up:

First of all, you know that you have a global limitation. No matter what information is contained, you only care about TYPE = STEEL. This is a global/background filter, a requirement to subset the data. That field goes into the FILTER field. For that matter, you have a limitation of DESIGN = SIT DOWN. That could easily be placed in the FILTER field. Filtering eliminates items that don't meet the filter. Frequently, you will be posed with an explicit question, and when you deliver the answer, it leads to more questions. I generally default to putting grouping elements into rows/columns so you can see them still (and be able to answer the next question that hasn't been asked).

Then understand how you want to aggregate data. The question says you want to know the overall average speed. Speed is the data point that goes in ∑ Values field, follow by a left click and changing "value field settings" to find average. This says that when you find multiple items of data that have some common attributes, what action do you want to do in order to summarize or aggregate that information. When it is a number, you usually want to count the number of instances, or sum the instances together, or (in this case) take the average of the values. These are all mathematical functions that are acting upon all instances of a data point (SPEED) to return a single piece of information on several underlying data points.

From there, you can easily add things to rows/columns as needed. This will group the information. Its performing the action that the phrase "for each amusement parks, what is the overall average speed" is asking for. Fields placed in rows/columns will GROUP the contents of the column together. In short, it looks at the list, and generates a list off of it where each item exists once (thus each amusement park is represented one time) and the aggregation function is applied to all instances that meet it. If there are 6 data points (coasters) in a park, the park name will show up one time on the left, and the 6 data points will be aggregated together in the values field (average is taken on the 6 points).

The row/column feature is a place to play around to tell the story the way you want to. You could layer all elements in the rows (hard to compare), or play with columns and row interplay to show how things intersect and keep the table tighter. This is where the fun comes in!

 

[sheetspread]

(Don't look here until after you've followed gmhumphr's suggestion):

 

[bencar]

I

First of all, you know that you have a global limitation. No matter what information is contained, you only care about TYPE = STEEL. This is a global/background filter, a requirement to subset the data. That field goes into the FILTER field. For that matter, you have a limitation of DESIGN = SIT DOWN. That could easily be placed in the FILTER field. Filtering eliminates items that don't meet the filter. .

Hi gmhumphr,

Thanks for your help. I can understand why DESIGN = SIT DOWN would also be in the filter along with TYPE = STEEL. But can you explain what you said, "Filtering eliminates items that don't meet the filter"? Thats confusing.

 

[sheetspread]

Filtering hides the rows you choose.

 

[gmhumphr]

Think of this hypothetical:

You have the dataset above, but there is one more roller coaster on the list. This one is different from the others in that its TYPE = TITANIUM. If you put TYPE in the FILTER and selected only TITANIUM from the list, the pivot table would ignore every other coaster on the list, since it is the one and only coaster that meets the criteria of TITANIUM. As far as the pivot table is concerned after that filter is put in place, you have 1 and only 1 record to display. There is no more data, and nothing to summarize/aggregate. If you threw other fields in ROWS/COLUMNS, it would only show the details of that one entry.

Now contrast that with the filter you are putting in place. There are MANY records for coasters that meet the TYPE = STEEL. All of those will be included in the pivot table, and will be summarized and aggregated upon.

 

[bencar]

Think of this hypothetical:

You have the dataset above, but there is one more roller coaster on the list. This one is different from the others in that its TYPE = TITANIUM. If you put TYPE in the FILTER and selected only TITANIUM from the list, the pivot table would ignore every other coaster on the list, since it is the one and only coaster that meets the criteria of TITANIUM. As far as the pivot table is concerned after that filter is put in place, you have 1 and only 1 record to display. There is no more data, and nothing to summarize/aggregate. If you threw other fields in ROWS/COLUMNS, it would only show the details of that one entry.

Now contrast that with the filter you are putting in place. There are MANY records for coasters that meet the TYPE = STEEL. All of those will be included in the pivot table, and will be summarized and aggregated upon.

Hi, gmhumphr,

There is no rollarcoaster with type = titanium in the data. Are we looking at the same thing?

 

[gmhumphr]

Hi, gmhumphr,

There is no rollarcoaster with type = titanium in the data. Are we looking at the same thing?

That's why I suggested a hypothetical. Imagine if the same data set you shared had one more line at the bottom, and it was for a TITANIUM roller coaster.

 

[bencar]

That's why I suggested a hypothetical. Imagine if the same data set you shared had one more line at the bottom, and it was for a TITANIUM roller coaster.

Ahh I see. Thanks man. Could you please answer this question for me?
https://www.mrexcel.com/forum/excel-questions/1049860-keyboard-shortcut-return-top-sheet.html