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

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

    Daniel Kaplan

      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 codeSeating CategoryNumber of tickets
      2013-12-04 Wed(E)-1204PEVE1st Balcony Ct. Price 33
      2013-12-04 Wed(E)-1204PEVE1st Balcony Ct. Price 40
      2013-12-04 Wed(E)-1204PEVE1st Balcony Lt. Price 30
      2013-12-04 Wed(E)-1204PEVE1st Balcony Rt. Price 33
      2013-12-04 Wed(E)-1204PEVE2nd Balcony Jump Seats Price 74
      2013-12-04 Wed(E)-1204PEVE2nd Balcony Ct. Price 55
      2013-12-04 Wed(E)-1204PEVE2nd Balcony Ct. Price 60
      2013-12-04 Wed(E)-1204PEVE2nd Balcony Lt. Price 56
      2013-12-04 Wed(E)-1204PEVE2nd Balcony Rt. Price 56
      2013-12-04 Wed(E)-1204PEVEOrchestra Price 10

       

      Dataset 2 example

      Seating CategoryCapacity
      Orchestra Price 1640
      Orchestra Price 2360
      Orchestra Price 3228
      1st Balcony Ct. Price 4187
      1st Balcony Rt. Price 4142
      1st Balcony Lt. Price 4146
      1st Balcony Ct. Price 5119
      2nd Balcony Ct. Price 6199
      2nd Balcony Rt. Price 6105
      2nd Balcony Lt. Price 6105
      2nd Balcony Ct. Price 792
      2nd Balcony Jump Seats Price 840
        • 1. Re: How do I calculate a "% of total" using two datasets
          Steve Taylor

          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
            Daniel Kaplan

            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 codeTickets to remove
            2013-12-04 Wed(E)-1204PEVE15
            • 3. Re: How do I calculate a "% of total" using two datasets
              Steve Taylor

              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