3 Replies Latest reply on Aug 5, 2016 8:37 AM by Steve Taylor

# How do I calculate a "% of total" using two datasets

Hello,

I have two datasets. The first contains number of tickets sold for a given concert (concert code) in a given seating category. The second contains total available seating (capacity) for each seating category.

I want to create a new variable that sums the total number of tickets for every unique concert code and seating category and divides this by the total capacity for that seating category (i.e. calculate % of total capacity filled in each seating category for any given concert code). My attempt at a calculated field returns some strange results. Can anyone please help? Thank you!

Dataset 1 example

 Concert code Seating Category Number of tickets 2013-12-04 Wed(E)-1204PEVE 1st Balcony Ct. Price 3 3 2013-12-04 Wed(E)-1204PEVE 1st Balcony Ct. Price 4 0 2013-12-04 Wed(E)-1204PEVE 1st Balcony Lt. Price 3 0 2013-12-04 Wed(E)-1204PEVE 1st Balcony Rt. Price 3 3 2013-12-04 Wed(E)-1204PEVE 2nd Balcony Jump Seats Price 7 4 2013-12-04 Wed(E)-1204PEVE 2nd Balcony Ct. Price 5 5 2013-12-04 Wed(E)-1204PEVE 2nd Balcony Ct. Price 6 0 2013-12-04 Wed(E)-1204PEVE 2nd Balcony Lt. Price 5 6 2013-12-04 Wed(E)-1204PEVE 2nd Balcony Rt. Price 5 6 2013-12-04 Wed(E)-1204PEVE Orchestra Price 1 0

Dataset 2 example

 Seating Category Capacity Orchestra Price 1 640 Orchestra Price 2 360 Orchestra Price 3 228 1st Balcony Ct. Price 4 187 1st Balcony Rt. Price 4 142 1st Balcony Lt. Price 4 146 1st Balcony Ct. Price 5 119 2nd Balcony Ct. Price 6 199 2nd Balcony Rt. Price 6 105 2nd Balcony Lt. Price 6 105 2nd Balcony Ct. Price 7 92 2nd Balcony Jump Seats Price 8 40
• ###### 1. Re: How do I calculate a "% of total" using two datasets

Hello Daniel

One way would be to use data blending (because the two data sets are different levels of aggregation).

In the attached workbook I've connected to your two tables separately (I had to change some numbers in the first table because between the two there were only 3 matches in seating category and those matches had 0 tickets).

Tableau automatically recognises the field to blend on (seating category) so the calculation below gives the % seats sold:

Hope that helps

Steve

• ###### 2. Re: How do I calculate a "% of total" using two datasets

Very helpful - thanks so much Steven.

One follow up question: I have a third dataset (e.g. below) which lists a number of tickets for a specific concert code that I first want to remove from the total ticket sum for that particular concert and then work out the % of total across all seating categories. The calculation would be something like (sum (number of tickets) - sum (tickets to remove))/ sum(capacity) except that now I would need to blend on the concert code field, not the seating category field. How would I go about doing this?

Thanks again!

Dataset 3 example

 Concert code Tickets to remove 2013-12-04 Wed(E)-1204PEVE 15
• ###### 3. Re: How do I calculate a "% of total" using two datasets

Hi Daniel

Again you can blend the "remove tickets" data to the seats sold data using concert code, ensuring that the first field you add to the view is from the seats sold data source (which makes it the primary) you can then bring in capacity and tickets to remove from the other tables.

HOWEVER - You'll now need to be careful with levels of aggregation. Your Capacity is at Seating Category level but your tickets sold are aggregated to Concert. Therefore, as I've shown with sheet 3, the tickets to remove value is displayed as the same for all seating categories.

It really depends upon what level of detail you want to report on. If you need to show Seating Category then you can't really include the tickets removed but if you're aggregating up to concert level then you can show a sum of the capacity and number of tickets sold in the same view.

Steve

1 of 1 people found this helpful