Calculating an Average Capacity Sold for a range of Theatre Shows

[Sam_Theatre]

Hello Everyone,

Sam here, very much an amateur on PowerBI. I've come across a problem that has me befuddled which I'm hoping one of you might be able to help me with. I work for a Theatre (think drama not healthcare) and so I use PowerBI to visualise lots of our data. One thing I've been really struggling with however is how to show the average % of the tickets we've sold. It seems really obvious (I've simplified this so that it helps me!)...

• If I have a theatre that has 10 seats and I sell 5 of them then I've sold 50% of my capacity (obvious right!).
  
  

However, we're a theatre with multiple venues which all have different capacities and the data outputs in an awkward way. Again a bit of a simplified table version I made in Excel of this...

So in this demo data set each row represents 1 ticket that we've sold.

So, as you can see we have...

• 2 shows (Uncle Vanya and Comedy Club)
• 3 performances (1st, 2nd and 3rd March)
• The capacity for Uncle Vanya is 8 seats (of which we've sold 4 on Mon and 2 on Tue) and the capacity for the Comedy Club is 4 seats (of which we've sold 3)

Our average capacity sold for Mon is 50%, Tue is 25% and Wed is 75%. The average capacity for Uncle Vanya is 37.5% and Comedy Club is (still) 75%. But our average capacity for all the events combined is 45%.

Finally, here's the less simplified visualisation sheet I've been trying to make with some actual figures in (that aren't quite right). The big gauge at the top is meant to show all the events combined and then the table has each individual's show's capacity.

At the moment Tickets Sold has come from the count of rows with the relevant show and then Capacity is the MAX figure in the row data for each relevant show.

I've been all over the place pulling my hair out with it. If anyone can give any advice or help direct me the right way I'd really appreciate it! I've been reading LOTS of articles and I know there's lots of super-smart people on here who (fingers crossed) might be able to bail me out.

Thanks, Sam

[Sam_Theatre]

Hey!

Of course - hopefully I've done it right 🙂

Best, Sam

Seat	Show	#Performance	#Sold	#Capacity
A1	Uncle Vanya	Mon 1 March	4	8
A2	Uncle Vanya	Mon 1 March	4	8
A3	Uncle Vanya	Mon 1 March	4	8
A4	Uncle Vanya	Mon 1 March	4	8
A1	Uncle Vanya	Tue 2 March	2	8
A2	Uncle Vanya	Tue 2 March	2	8
A1	Comedy Club	Wed 3 March	3	4
A2	Comedy Club	Wed 3 March	3	4
A3	Comedy Club	Wed 3 March	3	4
Total sales	9
Total performances	3
Total capacity	20
% Capacity Sold	45%
What I'd like to have…
45%	% Capacity Sold for All Event
Show	#Performance	% Capacity Sold
Uncle Vanya	Mon 1 March	50%
Uncle Vanya	Tue 2 March	25%
Comedy Club	Wed 3 March	75%
All shows		45%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Sam_Theatre]

Cheers Ashish!

It's a really good solution for the show % capacities but doesn't quite work for the totals for the combined shows... The % capacity sold for all the combined shows should be 45% (the total available capacity for the 3 shows is 20 and then the number of tickets sold is 9).

It almost needs the max capacity of each unique date calculating and then adding together (to get the total available) then the solution for number sold works perfectly with that

Best, Sam 

[Ashish_Mathur · Accepted solution]

Hi,

Please download the revised PBI file from here.

Hope this helps.

[Sam_Theatre]

Ashish this is brilliant! Thank you!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.  Clearly show the expected result there in a simple Table format.