Pivot Table - Averages Puzzle

[Lister00]

Hi All

I have a spreadsheet with 10,000s of Theatre Box Office records. Each record is a count of the number of tickets sold by Show, Date, and Ticket Type. For example some records may look like this:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Show Type[/TD]
[TD]Show Name[/TD]
[TD]Performance Date[/TD]
[TD]Day[/TD]
[TD]Ticket Type[/TD]
[TD]Tickets Sold[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Discount[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]8/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

I would like to summarise in a pivot table the AVERAGE sales on a Monday for a Christmas Show. This should be fairly simple. I would make AVERAGE "Tickets Sold" the Value in the Pivot Table, "Day" the Column and "Show Type" the Row, and "Ticket Type" a sub-row. Such a pivot table may look like this:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]Day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[/TR]
[TR]
[TD]

• Full Price

[/TD]
[TD]71[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

• Discount

[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Lets look at that Full Price average of 71. That is calculated by summing the "tickets sold" of all records matching the criteria "Christmas Show", "Full Price" and "Mon" and dividing by the number of records that match those criteria.

In this case (50 + 65 + 100) / 3 = 71

However!

Annoyingly some records relate to the same performance and ticket type. For example the top two records in the table.

We actually sold 115 Full Price tickets for the performance on 1/1/2018. However it is split into two records as they had different sale points. To get a correct average the Pivot table actually needs to add these two records together before calculating the average.

The Correct maths would be ((50+60)+100) / 2 = 105

My first instinct was to group the records by adding another column with a "Performance Code" calculation. So the new table looks like:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Show Type[/TD]
[TD]Show Name[/TD]
[TD]Performance Date[/TD]
[TD]Day[/TD]
[TD]Ticket Type[/TD]
[TD]Tickets Sold[/TD]
[TD]Performance Code[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]50[/TD]
[TD]Performance 1[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]65[/TD]
[TD]Performance 1[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]1/1/2018[/TD]
[TD]Mon[/TD]
[TD]Discount[/TD]
[TD]25[/TD]
[TD]Performance 2[/TD]
[/TR]
[TR]
[TD]Christmas Show[/TD]
[TD]A Christmas Carol[/TD]
[TD]8/1/2018[/TD]
[TD]Mon[/TD]
[TD]Full Price[/TD]
[TD]100[/TD]
[TD]Performance 3[/TD]
[/TR]
</tbody>[/TABLE]

But how I translate this into a working Pivot table... I have no idea. I am stumped. I feel like there needs to be a middle table that summarise the records into 1 record per performance. But not sure how best to do that.

Any advice, greatly appreciated.

 

[Lister00]

Sorry. I made a typo. The correct Maths would be: ((50+65)+100) / 2 = 102.5

 

[Lister00]

Get it right man! The correct Maths would be: ((50+65)+100) / 2 = 107.5