14 Replies Latest reply on Nov 12, 2014 12:36 PM by Matt Lutton

    Percent Change Calculation Across Measures

    emily.arbuckle

      Hi All,

       

      I would like to show a percent change calculation on a table across measures within my Tooltip . I have found support articles about using table calculations across dimensions, but not across measures unfortunately. Is there a way to do this?

       

      To exemplify, I have a table that shows the following metrics (columns) broken out by week (rows): Spend, Revenue, Conversions, ROI, Impressions.

       

      Ideally, when I hover over the week's Spend figure, I would like to see the W-o-W percent change for that metric. As I move across the table, I would like to see Revenue, Conversions, ROI, and Impressions percent changes, respectively.

       

      Any guidance on this issue would be greatly appreciated.

       

      Thank you!

      Emily

        • 1. Re: Percent Change Calculation Across Measures
          pooja.gandhi

          Something like that should be done by clicking on the tooltip button on the marks card. Click insert and add measures of your preference.Capture.PNG

          • 2. Re: Percent Change Calculation Across Measures
            Matt Lutton

            Hi Emily:


            Can you mock up a workbook using Superstore Sales?  Its far easier to guide you with an example workbook to work from.  Cheers.

            • 3. Re: Percent Change Calculation Across Measures
              emily.arbuckle

              Thanks, Pooja.

               

              I'm familiar with adding those table calculations to the Tooltip. But is there a way to make the Tooltip change as I hover over different columns (metrics)?

               

              For example, if I'm hovering over Impressions, I only want to see the W-o-W percent change for Impressions, not the percent changes for all the other metrics too. Is there a way to make the tool tip dynamic, so that it only shows percent change for the metric I'm hovering over?

               

              Thanks!

              Emily

              • 4. Re: Percent Change Calculation Across Measures
                emily.arbuckle

                Hi Matthew,

                 

                Thanks for your help! Not quite sure how to attach a workbook here, but below is an image of sample data:

                 

                11-12-2014 10-33-36 AM.png

                 

                It would be great if I could have year-over-year percent change show for each metric in the tooltip, as I move across the table.

                 

                Best,

                Emily

                • 5. Re: Percent Change Calculation Across Measures
                  Matt Lutton

                  To attach within a reply, choose "Use advanced editor" in the top right corner, then "Attach" will appear next to the "@ Mention" option in the bottom right corner.

                   

                  I'm not sure if what you want is possible, but I'm happy to take a look.

                  • 6. Re: Percent Change Calculation Across Measures
                    emily.arbuckle

                    Hi Matthew,

                     

                    Thanks for your help! I've attached the document here.

                     

                    As you'll see, I've created % change table calculations for each metric and added them to the Tooltip; however, they all show up at the same time instead of surfacing the appropriate measure that the user is hovering over.

                     

                    Would greatly appreciate any tips you could provide!

                     

                    Thanks,

                    Emily

                    • 7. Re: Percent Change Calculation Across Measures
                      pooja.gandhi

                      Hi Emily,

                       

                      I see that you are using 8.2.0 version and wouldn't be able to open my workbook. I experienced the same issue when it is in table format. However, changing it to a graphical form solves that problem. Because you can modify tooltips in the marks card for individual measures.

                      Capture 1.PNGCapture 2.PNG

                      1 of 1 people found this helpful
                      • 8. Re: Percent Change Calculation Across Measures
                        Matt Lutton

                        Yes, Pooja is correct above -- however, it IS possible that you could accomplish the exact same thing with a multiple axis crosstab -- I don't have time to set it up right now, but if you place each measure on its own "Axis", you'll get the same result shown above in Pooja's Bar Chart view, but in a Multiple Axis Crosstab instead.  This takes time to set up, and has its own unique limitations (same columns widths, more effort to create and maintain, etc.)

                         

                        Everything you need to set something up is shown here (download the TWBX and study/read the captions if you want to replicate the work):

                         

                        http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/conditionalformattingv4/Introduction

                         

                        If you start setting this up, and run into trouble, feel free to post a TWBX here and I am happy to take a look and help guide you.  This is the only way I know around your problem, using a text table--other than separating out each measure on a new worksheet (this also would work, but would have even more limitations). 

                         

                        Cheers

                        1 of 1 people found this helpful
                        • 9. Re: Percent Change Calculation Across Measures
                          Matt Lutton

                          Here is the start of a TWBX that utilizes multiple sheets to accomplish what you want.  You could extend this further, by only returning the % difference for relevant years, but I didn't bother with that (so when you hover over 2010, the % change will be empty in the tooltip -- you could address that in the calculation itself if you wanted to).

                           

                          By separating out each measure, each now has its own individual tooltip.

                           

                          The idea is the same, when creating a multiple axis crosstab, as shown in the link I provided above.  By utilizing a dual axis for each individual measure, we give that measure its own tooltip/pane.

                           

                          I hope this helps.  I would avoid either of these methods, if at all possible, and use a different method for visualizing the results.  But since you've asked, these are two methods that will allow you to accomplish what you want in a crosstab format.

                           

                          2sheets.png

                          NOTE: The attached workbook is a Version 8.1 TWBX.

                          • 10. Re: Percent Change Calculation Across Measures
                            emily.arbuckle

                            Hi Matthew,

                             

                            Thanks for sending -- unfortunately, I cannot open your document because it was created in a newer version of Tableau.

                             

                            Is one of these methods easier than the other? If it's the latter solution (multiple sheets), is there a support link you could pass along?

                             

                            Thank you!

                            Emily

                            • 11. Re: Percent Change Calculation Across Measures
                              emily.arbuckle

                              Thank you, Pooja!

                               

                              This is good to know. If the other methods below prove to be too time consuming, may be best to use this structure.

                               

                              Best,

                              Emily

                              • 12. Re: Percent Change Calculation Across Measures
                                Matt Lutton

                                What version are you using?

                                 

                                Both techniques require time to set up and can be difficult to maintain -- I personally wouldn't recommend either, but the multiple axis crosstab offers a bit more flexibility in this case, I think.  With multiple sheets, formatting and filters can be difficult to maintain, depending on what else you are doing with the view (interactivity, filters, etc.)

                                 

                                Its like the screenshot shows--every sheet is set up the exact same way, but on each sheet, we only include on measure, then we place them together on a dashboard, while hiding the title and forcing column headers. 

                                 

                                This is the first sheet I built:

                                Sheet 1 Order Quantity.png

                                 

                                And the second sheet (same structure, but I've hidden the Year so it only appears in the first sheet):

                                Profits.png

                                 

                                I hope this helps.    

                                • 13. Re: Percent Change Calculation Across Measures
                                  emily.arbuckle

                                  Ah I understand, now. Thanks for the clarification!

                                   

                                  I have one section that scrolls so may be tough to do this with multiple worksheets -- would result in multiple scroll bars I imagine. I agree that it may be easier to find a better way to represent this visually.

                                   

                                  Thank you very much for your help on this! It's unfortunate that there isn't an easier way to make this happen, but good to have the final answers.

                                   

                                  Best,

                                  Emily

                                  • 14. Re: Percent Change Calculation Across Measures
                                    Matt Lutton

                                    Yes, multiple scrollbars should be avoided.  You should check out the multiple axis crosstab method, if this is something you REALLY need.  Alternatively, I'd suggest using a visual representation, like the bar chart Pooja showed earlier.

                                     

                                    Crosstabs are generally poor visualization choices, particularly when you have other options that can communicate the same data much more clearly.  People tend to ask for crosstabs because they are used to Excel.  Perhaps if you show them something better, something more visual, they will get away from thinking about crosstabs?