4 Replies Latest reply on Apr 6, 2016 7:55 AM by Sarah Mueller

    Making a pie chart with changing denominator

    Sarah Mueller

      Hi Tableau Community!

      I'm trying to make a pie chart that has a changing total. In the attached example, I have expenses broken down for a $3000 budget. What I want to do is make a chart that shows how a small budget cut could actually be a big chunk of discretionary spending if all of the non-discretionary parts of the budget are removed. For example, a total budget reduction of $100 would be 3.3% of the total budget. If you remove $1100 in rent, typically "non-discretionary" the $100 cut now makes up a larger portion of the remaining discretionary budget. The wedge in the pie chart for the cut is still $100, but the total is now $1900, so the cut would be 5.3% of the remaining budget.

       

      Thoughts on how to do this? I can do it in excel if I prioritize the order of least discretionary to most discretionary spending, but I would like to have a quick filter that I can select and deselect which parts I want to count as non-discretionary or discretionary based on whose priorities are being used.

        • 1. Re: Making a pie chart with changing denominator
          Brent Pristupa

          I am not sure if I completely follow what you want, but is it something like this? I am not sure on how to add a wedge for the amount cut. If you tell me what version of Tableau you have I can create and attach a packaged workbook.

          changing_total_pie_percentage.png

           

          For now, I will just explain what I did. I created an integer parameter called 'budget cut'. I then created a calculation 'Budget'

          changing_total_pie_percentage_2.png

          Then I created a calculated field called 'Percent of Budget'

          changing_total_pie_percentage_3.png

          And brought those two calculations onto a view. I then put the pie chart you created and the sheet that I created onto a dashboard. Finally, I set the item list to 'apply to all'

          changing_total_pie_percentage_4.png

          Now when you select/deselect an item, the 'Budget' updates and the Percent recalculates to the new 'Budget'

          • 2. Re: Making a pie chart with changing denominator
            Brent Pristupa

            To add the budget cut wedge, you have to create a new row within Excel. I called it 'Budget'.

            changing_total_pie_percentage_5.png

            Then you have to created a calculated field to case the Item list, assigned the value you calculated for each item. For 'Budget' I used the Budget Cut parameter that I created in the previous post.

            changing_total_pie_percentage_6.png

            Then I brought the new pie chart onto a dashboard along with the sheet I created earlier (sheet 2). There is now a budget wedge that represent that amount cut. Still not sure if this is doing the right thing, though.

            changing_total_pie_percentage_7.png

            • 3. Re: Making a pie chart with changing denominator
              Sarah Mueller

              Hi Brent Pristupa

               

              That's kind of what I was thinking... I think the calculations shown in the last screen shot, Sheet 2 looks right, but the the image in Sheet 4 looks like the budget section is 1/4 of the pie rather than 1/3. The amount of the cut isn't what is changing, it's which categories are vulnerable to the cut. So if the original budget is $3000, the $100 cut would be (100)/(3000)*100 for the percent of the pie. But if rent is removed, i.e. not vulnerable to the cut, the budget would change to $1900, so the $100 cut would now be (100)/(1900)*100 for the percent of the pie. Does that make sense?

               

              I have tableau 9.0 if it's easier to send a workbook than screen shots

               

              Thank you!!

              • 4. Re: Making a pie chart with changing denominator
                Sarah Mueller

                Here's an updated workbook (9.2) that shows the progress.

                 

                The pie chart with the slider has calculations done in excel, locking in the order in which pieces are selected and deselected for the part of the budget that is vulnerable to cuts. I would like to have a view that looks like this, but with check boxes rather than a slider, so I could select which pieces were included and not included in different orders

                 

                The bar sheet has the correct calculation, but is not visually easy to see the change over different conditions.

                 

                The pie sheet shows the all the parts of the budget, but includes the cut as part of the budget, so the total size of the pie is off. This isn't noticeable when everything is selected, but as the total pie gets smaller, the error gets bigger. The percent calculation is also off.

                 

                Thanks for any suggestions!!!