7 Replies Latest reply on Jan 23, 2014 9:11 AM by caitlin.donaldson

    How can I add a line in my chart indicating the overall KPI?

    malou.puyod

      I have a line chart which displays KPI of the dimension I chose (e.g. Subscriber).  Each date contains the KPI of individual subscribers.  How can I add an overall KPI line?  The overall KPI line displays the KPI of all subcribers for the specific dates.  Sample formula is SUM(Numerator)/Sum(Denominator) in %.   Help please.

        • 1. Re: How can I add a line in my chart indicating the overall KPI?
          Tracy Rodgers

          Hi Malou,

           

          One way to do this might be to add a reference line. The following article provides further information on how to do this:

           

          http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#reference_lines.html

           

          -Tracy

          • 2. Re: How can I add a line in my chart indicating the overall KPI?
            malou.puyod

            Thanks Tracy for your reply.  I tried that specially "per cell" reference line because the KPI value of the dimension varies per day but it won't connect a single line.  The other reference line uses straight line which should not be the case because my overall KPI value per day varies.

             

            My client wants to see the values of the dimension per day and its corresponding value + the overall value of that KPI.  Eg. value per product per day + overall KPI of all products per day (overall value creates single line)

            • 3. Re: How can I add a line in my chart indicating the overall KPI?
              caitlin.donaldson

              Is there a possibility that you can show a screenshot or share the TWBX file so we can see how it is setup?

               

              How are you differentiating each subscriber?  By color?  How is your data structured?  You should be able to add the KPI variable again to get an overall number. 

               

              I put together some fake data and included screenshots below so you can see what I'm talking about.  In the example I gave, I created a new calculated field called "Overall KPI", although you can use the original KPI field you already have and don't need to create a new variable.  I did this so you can see the placement of everything.  When you want to add the second KPI variable, drag the KPI field to the right side of the graph until you see a dotted line.  Then drop it down and the graph will create a dual axis chart.  Then just hide the axis on the right hand side so you don't have the dual axis by right clicking on it and clicking "Show Header."  Before you remove the second axis, just make sure the two axes match in scale. 

               

              In my final graph, the black line is the overall KPI whereas the rest of the lines are each of the subscribers.  I made the overall line black by going to the Marks card, clicking the SUM(Overall KPI) box, then removed the color by Subscriber and by Measure Name so I was left with a single overall line. 

               

              2014_01_21 KPI Setup.JPG.jpg

              2014_01_21 KPI Final Graph.JPG.jpg

               

              I hope this helps!  Let me know if you need a better explanation.

              • 4. Re: How can I add a line in my chart indicating the overall KPI?
                malou.puyod

                On the first screenshot,  it shows the KPI of each Subscriber (color coding) per day.  It's a line chart.  It shows circle or dot per subscriber if his Failed Call occur only on a specific day but if it's recurring on 2 or more days then it forms a single/continuous line.

                 

                pic01.jpg

                 

                On the second screenshot, I want to add this overall KPI line to the screenshot above so it will only be 1 chart.

                 

                pic02.jpg

                 

                If I tried to add the overall KPI as dual axis, it looks like the 1st screenshot.  No overall KPI line like this one:

                 

                pic03.jpg

                 

                Here are the sample formula:

                 

                OVERALL CSFR % :

                WINDOW_AVG((SUM([Numerator])/SUM([Denominator])),0,0)

                 

                CSFR % :

                (SUM([Numerator])/SUM([Denominator])

                 

                Let me know if you need more details.  Thanks for your help.

                • 5. Re: How can I add a line in my chart indicating the overall KPI?
                  caitlin.donaldson

                  In the last chart, would you be able to get rid of MASK ID as the color for CSFR (copy 3)?  Keeping the MASK ID as the color means you are layering the same points on top of each other and still differentiating between each subscriber.  That would be the first step.  Also, you should be able to create a dual axis graph with either the overall CSFR or the CSFR % so either one shouldn't be a problem.  Once the color by MASK ID has been removed, if you could re-post a screenshot, that would be really helpful.  Then it can be determined what else might be causing a problem.

                  • 6. Re: How can I add a line in my chart indicating the overall KPI?
                    malou.puyod

                    I think merging of those 2 charts are not possible.  The client would like to see the KPI of the user that occur within the day (1st chart above - Mask ID color code) and at the same time view in the same chart the overall KPI.

                     

                    Thanks for all your help.

                    • 7. Re: How can I add a line in my chart indicating the overall KPI?
                      caitlin.donaldson

                      You are able to remove the color for one variable, but not for the other.  Refer back to the screenshots I posted earlier and see how I was able to have color for only one variable.  The Marks card gives you the ability to customize how each variable is displayed.  You can set the color to all variables or just individual variables in the Marks card.