7 Replies Latest reply on Feb 28, 2012 8:59 AM by Mingzhe Bian

    About percentage of crosstab from different data sources

    Mingzhe Bian

      From this link I got the idea of how to combine different data sources to show crosstab

      http://kb.tableausoftware.com/articles/knowledgebase/multiple-sources-one-worksheet

       

      CombiningMultipleDataSourcesIntoOneView_Final.png

      However, after I get the table, want to expand it for percentage of column total. It just doesnt work from the Analysis.

      Can anybody give some comments on this? Thanks

        • 1. Re: About percentage of crosstab from different data sources
          Jonathan Drummey

          Without having your data and/or a packaged workbook, I can't say exactly. You also don't specify what "It just doesn't work from the Analysis." means. Are the options not available, does the view stop updating, do some measures get the red exclamation point next to them, or is something else happening?

           

          Data blending can be very mystifying. Looking at your view, I'm guessing that having the Product Type on your Level of Detail could be part of the issue, and/or what the calculated fields are. Where data blending fails is either when there's not enough detail for the blend to occur (nothing happens or wrong values are displayed), or too much detail (resulting in asterisks or overlapping text). But there's really not enough to go on based on your description and the screenshot.

           

          Jonathan

          • 2. Re: About percentage of crosstab from different data sources
            Mingzhe Bian

            Jonathan,

            Thank you for the reply. Actually all the data is from the Tableau Samples: Coffee Chain ans Superstore Sales.

            I just followed the example and see attached.

             

            PercentageError.png

            After combining the two data sources successfully, it doesnt allow me to calculate the percentage of the column total with red exclamation point by clicking Analysis-> Percentage.

             

            Any ideas?

            • 3. Re: About percentage of crosstab from different data sources
              Jonathan Drummey

              When uploading workbooks please use a Tableau Packaged Workbook, that automatically includes all data in the workbook. I had to reconnect to the data when I opened the .twb file - not a big deal since you'd provided the .xls and were using the Tableau sample data, but some extra steps just the same.

               

              See the attached for two examples of how to get the totals to work. Either way, there were two steps to get this to work:

               

              1. Work around a limitation of TOTAL(). The default Percentage of... calculation uses the TOTAL() function that executes SQL against a datasource (according to Joe Mako), and since your calculations were in one datasource but referring to values in another the TOTAL() calc was failing. One way around it was to directly drag the Sales pill from each secondary datasource and then do the Percentage of... on those fields, another was to manually write percentage of functions for Sales from each source. I'm not sure why Tableau couldn't figure that out itself.

               

              2. Trying to use the default Percentage of... calculations on the combined total of the two fields ran into the same TOTAL() issue, so the way around that was to use the WINDOW_SUM() function. The WINDOW_XXX functions run against the data returned to Tableau, so they can cross datasources. I set up three table calcs, one to get combined total of the two fields, another to get the grand total, and a third using those two to get the % of total.

               

              In one example, I based the totals on the functions you'd created, in the second example I used the LOOKUP() function to be able to set 0 values for cells where there was no data.

               

              For more information on table calculations, I recommend starting with http://www.tableausoftware.com/table-calculations and using the workbooks and links there. The Tableau videos are also a good start as well.

               

              Let me know if this works for you!

               

              Jonathan

              • 4. Re: About percentage of crosstab from different data sources
                Mingzhe Bian

                Yay, it works. Thank you Johnathan for the detailed explaination. It's extremely helpful.

                Appreciate your help very much.

                • 5. Re: About percentage of crosstab from different data sources
                  Mingzhe Bian

                  Jonathan, I have expanded your workbook to create some pie chart in practice.

                  However I can only show the volume, but not the percentage in the slices.

                  Can you let me know how to create the percentage on each of the slice withint the pie chart?

                  Please see attached. Thanks a lot

                  • 6. Re: About percentage of crosstab from different data sources
                    Jonathan Drummey
                    I couldn't figure out a way to get the Sales Works % of Total calculation to work properly, no matter what combination of compute using I tried. However, there's the Sales Combined % of Total field that I'd created for the "Using LOOKUP() - Works" worksheet. Setting that to compute along Product works just fine.
                    Pie charts are generally not good practice for data visualization. In this case, to viewer would have to switch between the pie and the legend to know what each circle referred to, unless you created a calculated field combining the Product and % of Total to useas the label, and then labels would start to get messy pretty quickly. Also, 7 different slices is often considered too many for viewers to be able to figure out the part to whole comparison. If you really want to use a pie, in a view like this I'd suggest sorting the slices from largest to smallest, setting the colors so the products from superstore sales are from the same color range, and the colors from the coffee chain in another range, then rearranging the labels (you can drag and drop) to get them to fit.
                    Bar charts generally communicate more accurately, so I also prepared an alternative bar chart view.
                    Jonathan
                    • 7. Re: About percentage of crosstab from different data sources
                      Mingzhe Bian

                      Thanks a lot for the reply, "computing by product" is the Key.

                      Also, apprecaite all your tips on choosing different charts.