10 Replies Latest reply on Jun 21, 2018 9:03 AM by Eric Mathis

    Reference Lines in Donut Charts

    danny ledford

      Tableau experts- I need to add a reference line to the donut chart to show the goal.  I couldn't figure out how to get the Tableau reference lines to work on the pie chart, so I ended up creating an additional measure with a value of <1% to represent the Goal (reference line).  I, also, had to create a couple of additional calculated measures to account for the gap b/w goal and actual, goal and 100%, and one more for when the actual cross over the goal.  Seems like a lot of work, but I think it gets the job done.  Can anyone tell me if there is a much more simple way of achieving this?

       

      See 78% Goal in screenshot below....

      Pie Charts Questions - Tableau Community.png

        • 1. Re: Reference Lines in Donut Charts
          Mark Fraser

          Hi Danny

           

          Your example above is the first time I have seen a target line in a doughnut chart in Tableau.

          You certainly did better than these >> donuts charts with targets - actual values

          Is it something you could share?

           

          In answer to your query, this is probably the closest (but i doubt any simpler or easier than your own) > The Vizioneer: Day 19: Donut Charts

           

          The reason that doughnut charts aren't directly/really supported in Tableau (they are a hack) is that they aren't considered 'best' practice for data visualization.

          There are lots of comments, blogs etc. this one, I found interesting > With Great Power Comes Great Responsibility (or, Think Before you use a Donut Chart) – Data Revelations

          and this is the most famous >> Tableau Tip: How to make KPI donut charts with Andy's thoughts on doughnut charts...

          I'm conscious I haven't answered your question, but I hope something in my reply is useful

           

          Cheers

          Mark

          • 2. Re: Reference Lines in Donut Charts
            danny ledford

            Thanks for the reply, Mark. 

             

            I am well aware of the advice against using pie charts and donut charts.  I normally steer clear, but the head honchos have asked for them.  However, one thing I've gotten into a habit of doing is finding a better way of highlighting the most crucial KPI's on an operational dashboard, so that they don't get lost in the mix somehow.  I find using a donut chart per KPI is a great visual.  In a relatively small area, you can provide the actual, a visual for what's left to reach actual, and the goal.  Since it's a donut with a hole in the middle, you have a place to add a little more context. I'm my case below, I've added where we were same time last year (PY).  If the company would let me, I'd color the blue line green/red depending if it had reached the goal line.

             

            Anyway, here is how I was able to get a 'goal line' in the chart:

            You have to create a total of five measures and place them in this order to work:

             

            Logic:

            1)  If Actual < Goal value (57%) THEN Actual ELSE Goal value.  This will bring actual only up to the value of goal if it exceeds.

            2)  If Actual < Goal value THEN Goal value - Actual ELSE NULL END.  This will plug the GAP between goal and actual as seen on Texas below.  Once actual reaches goal, it is null so it'll disappear.

            3)  Reference line to represent Goal = 0.01%.  Need just enough to see a line.

            4)  If Actual > Goal value THEN Actual - Goal ELSE NULL END.  This creates the extended actual beyond the red goal line as seen in the large KPI on the left, but renders null to make it disappear if Actual isn't greater than Goal.

            5)  1 - #1 - #2 - #3 - #4.  This creates the remaining portion as seen as grey in the charts below.

             

            Two goals on the same chart would probably require same methodology but require several more measures and likely not even worth it.

             

             

            I hope this was helpful!

             

            Danny

            2 of 2 people found this helpful
            • 3. Re: Reference Lines in Donut Charts
              Mark Fraser

              Danny, that's brilliant, many thanks.

              I am well aware of the advice against using pie charts and donut charts.  I normally steer clear, but the head honchos have asked for them.  However, one thing I've gotten into a habit of doing is finding a better way of highlighting the most crucial KPI's on an operational dashboard, so that they don't get lost in the mix somehow.  I find using a donut chart per KPI is a great visual.  In a relatively small area, you can provide the actual, a visual for what's left to reach actual, and the goal.  Since it's a donut with a hole in the middle, you have a place to add a little more context.

              Personally I agree, I am aware of best practices and respect them where possible, but sometimes we just need 'real world', best practice or not... the 'head honchos' (like that!) unfortunately most likely sign off on Tableau's licensing, so I'm well aware that sometimes you just have to give them what they want.

               

              If you don't mind, I'll take your instructions and create a 'wiki', its a way to share on the forum. I'll give you full credit, its yours, after all

              it maybe useful to someone else in the future... unfortunately it was too late for these >> donuts charts with targets - actual values but I will post to that thread a link once completed.

               

              Thank you for taking the time to provide the instructions, that's very thoughtful, especially as we didn't even answer your query!!

               

              Thanks again, and all the best!

               

              Cheers

              Mark

              • 4. Re: Reference Lines in Donut Charts
                danny ledford

                Mark-  No problem.  Several folks in the Tableau community have helped me tremendously, so I'm happy to give something back.  I'm not familiar with the wiki, so please post a link once you've added it.  I'd like to check it out!

                 

                Thanks again,

                 

                Danny

                • 5. Re: Reference Lines in Donut Charts
                  Corbin Supak

                  I just created this nearly the exact same way you did - within weeks of each other! Same vocabulary too, Actuals, Goals, Pace. I'm using the donut as a 'whole', meaning the Goal is the full circle. I've got 5 Measures as slices: Actual pre-pace, Gap pre-pace, Pace Marker (=1% of the whole), Actual post-pace, Gap post-pace. Only problem with the method for me is labeling values of the pie pieces ($ values in my case), since I've sliced up the values I'd want to display in to pre and post parts, so the label values are incorrect. But like your example, you can use the white circle placeholder mark as your label space inside the donut, and use the original fields to label that mark.

                   

                  I think donuts are great for two values, such as pace to goal. Circular shape emotes sense of whole or cycle, completion. It can look distinctive next to lots of other bars in a dash. And it functions well as a small multiple.

                  • 6. Re: Reference Lines in Donut Charts
                    Curtis Looney

                    Danny,

                     

                    Thanks for providing this.  Is there any chance you can provide some clarity as to how you were able to get this into a Donut Chart?  I have followed the steps provided but I am still unable to get the reference line to show up.  Which calculations go where?

                     

                    Thanks,

                    • 7. Re: Reference Lines in Donut Charts
                      Philip Byrne

                      Hi I can create every other measure of this except for the reference line.

                      Can anyone explain what I need to do?

                       

                      Thanks in advance,

                      Philip

                      • 8. Re: Reference Lines in Donut Charts
                        Cassie Johnson

                        Hi Danny,

                         

                        I know this is an old thread, but if you would be able to provide a packaged workbook that showed how you set up this viz, I'd be so grateful!

                         

                        These types of charts being the "black sheep" of data visualization and all, I haven't found any I like as well as yours, but can't seem to replicate them. They're not something I ever do, but because I'll be using them to illustrate goals that are all percentages, they show one KPI per chart and do not need to be compared, and the client has specifically requested these for their overview dashboard, I don't feel so bad using them!

                         

                        Again, thanks so much!

                        • 9. Re: Reference Lines in Donut Charts
                          kumud.pandey

                          I intend to create similar chart, having reference line in the donut chart. There are three fixed segment in donut chart, threshold, target, exceed. I want to add the reference line for actual.So, say Threshold = 10, target =20 and exceed = 30 and actual (which varies on monthly basis) is 25 for current month then reference line will fall under exceed segment. Let me know if anyone was successful to create the chart in similar line

                          • 10. Re: Reference Lines in Donut Charts
                            Eric Mathis

                            Thanks for this viz Danny!

                             

                            If anyone was getting close to the answer but not getting slice #5 to appear correctly, make sure in calculation #5 you wrap #2 and #4 in ZN() because #5 will be nulled out if any component of it is null.