6 Replies Latest reply on Apr 15, 2012 6:34 PM by Jonathan Drummey

    Summarizing data using stacked bars

    Tandra Bidyananda

      Attached is a snapshot of a workbook that uses a stacked bar. Since this calculation is calculated for each PIN (which is included in the Level of Detail shelf), I see lines dividing the values for each PIN. Is there any way to remove these lines and display a sum of the value on top of each bar? E.g. for the country DE, the number on top of the bar should be 53.

        • 1. Re: Summarizing data using stacked bars
          Jonathan Drummey

          Hi Tandra,

           

          You can remove the lines (which are borders) by clicking on the Color drop-down on the Marks shelf and turning off borders - Under Effects, set Border to None. When you have a discrete (blue) pill on the Level of Detail for a bar chart, Tableau defaults to drawing borders around each instance of the discrete value in the bar.

           

          As for adding a total to the top of each bar, you can add a reference line by right-clicking on the vertical axis, select Add Reference Line, set the value of the line to be your measure and the aggregation to be Sum (since you're using a table calc) or Total (for other aggregates). Set the label to Value and click OK.

           

          Tableau uses the displayed data in the axis to determine how to resize the view, so sometimes the text for reference lines can get cut off. In that case, you can play around with manually resizing the view, manually setting axis sizes, etc. and/or create calculated fields that return the correct values. Then you can put that calculated field on the Level of Detail shelf and add a reference line for that value.

           

          Jonathan

          • 2. Re: Summarizing data using stacked bars
            Tandra Bidyananda

            Thanks Jonathan. That works perfectly!

            • 3. Re: Summarizing data using stacked bars
              Tandra Bidyananda

              Would there be a smart way to keep the sorting on too? Since I am using a calculated field to plot the sum, the sorting gets skewed every time I change my code selection. Also the reference line labels seem aligned to the left no matter how I change my view. Anything that can fix that?

               

              Thanks!

               

              Tandra

              • 4. Re: Summarizing data using stacked bars
                Jonathan Drummey

                Without seeing your data, I'm not sure about the sort, could you post a packaged workbook with some sample data? You can change the alignment of the reference line labels by right-clicking on the reference line and choosing Format... from the context menu.

                 

                Jonathan

                • 5. Re: Summarizing data using stacked bars
                  Tandra Bidyananda

                  Jonathan,

                   

                  Here is the workbook.

                  Thanks!

                   

                  Tandra

                  • 6. Re: Summarizing data using stacked bars
                    Jonathan Drummey

                    Hi Tandra,

                     

                    Since you're using a table calculation, Tableau can't use the built-in sorting options. The workaround is to take advantage of the default sorting of headers. Since Tableau's default sort on numeric values is ascending, I created a version of your Total DTCS calculation that returns a negative number, then put that on the left of the columns shelf and set the Compute Using to Code, like the existing calc. Turning off Show Header then leaves you with an effectively sorted view. You'll also want to hide the pane borders that Tableau automatically inserts as well.

                     

                    However, the sort doesn't work in all cases, for example with Code Filter 521,000.9, where the maximum value is 9 and yet a few of the Codes are showing 18. Something on (or a field that's not on) the Level of Detail shelf is causing the table calculation to return 2 rows and since the marks are stacked, 2 * 9 = 18. I'm not sure what that is, you're going to have to work through your data to figure that one out.

                     

                    Jonathan