9 Replies Latest reply on Feb 17, 2016 2:57 PM by Joe Oppelt

    Sum Grand Total

    Shanaka Gunaratna

      Hi All

       

      I have an issue where applying "Show Row Grand Totals" where I have used a color to differentiate same measure against a dimension (to highlight a current month in time series). When applying row grand total it will breakdown the total based on the color (see attached image). Any idea to resolve this. I cant use 2 sheets since there is a hierarchy

        • 1. Re: Remove color in Grand Total
          Joe Oppelt

          Consider doing a conditional formatting instead of putting a dimension on the color shelf.

           

          Create two calcs.  One will be:

           

          Calc1:

           

          IF (whatever condition is met for orange) THEN [name of measure value field here] END

           

          Calc2:

          IF (orange condition is NOT met) THEN [name of measure value field here] END

           

          Put your [measure value field] on your text shelf (as you currently have it now).  Also put the two calcs on your data shelf.

           

          Then edit the label.  Remove [measure value] from inside the label editor and place your two calcs in there instead. Immediately abutting, no spaces between them, on the same line.  Format one to be orange, and format the other to be blue.  The two calcs will either have a value, or be null, depending on whether or not it meets the orange criteria.  So only one value will actually display, and it will either be orange or blue as appropriate.

           

          Your row total will still calc as you expect, and will have only one value per row.

           

          If you need help with this, post a sample workbook and I'll show you what I'm talking about.

          • 2. Re: Remove color in Grand Total
            Joe Oppelt

            Sorry, that's not going to work.

             

            I played with this in a superstore example.  It wasn't as simple as I thought.

             

            See attached.  I had to use a table calc to sum across, and then use INDEX to tell tableau only to display the first of the two grand totals.

             

            I think I over-complicated this, but I couldn't find anything else that worked.

             

            Take a look, and then ask questions.

            • 3. Re: Remove color in Grand Total
              Shanaka Gunaratna

              Hi Joe

              Thanks for your help. Btw, I think you forgot to attach the file. 

              • 5. Re: Remove color in Grand Total
                Joe Oppelt

                I failed to mention ... that is a version 9.0 workbook.

                • 6. Re: Remove color in Grand Total
                  Shanaka Gunaratna

                  Hi Joe

                  Thanks for the workbook. But Grand total is not correct it seems. I think it will just display grand total of one color category

                  • 7. Re: Remove color in Grand Total
                    Joe Oppelt

                    I just typed up a long reply to explain why the grand total will be colored like one of the color categories, and then I re-created the workbook to make a 3rd color (for the grand total).  I have attached that.

                     

                    But then I realized that you were saying that the grand total that was showing in that workbook was the grand total value (not the color) for one of the categories.


                    And it was.  That was an error on my part.

                     

                    In re-creating this to add a 3rd color, it made tableau address the calcs differently because the color calc became a table calc.  In fact, now the totals are correct.  (Previously Asia somehow had the grand total as its total.)

                     

                    In the attached, sheet 1 now has two colors for the various continents, and a 3rd color for the grand total.  And it's actually much simpler than the first pass.  It just uses the default "Table(across)" for all the table calcs.  Numbers are right.  (See sheet 2 for a simple non-colored viz of the same data.)

                     

                    But even  simpler, I realize why you were getting two totals in your grand totals column.  I'll bet the field you were using on the colors shelf was a dimension.  Change it to a measure.  (Right click, select "convert to measure".)  Tableau is partitioning by the dimension values.  It won't do that with a measure.  If the measure is numeric, be sure to change the pill on the colors shelf to "Discrete" instead of continuous.

                    1 of 1 people found this helpful
                    • 8. Re: Remove color in Grand Total
                      Shanaka Gunaratna

                      Hi Joe

                      Sorry to bother you again, I couldn't find the attached workbook

                      • 9. Re: Remove color in Grand Total
                        Joe Oppelt

                        At the bottom of my post (and the first one that you didn't see the attachment for) is a box with a paperclip picture.  That's where the attachment is.  Click it.

                        1 of 1 people found this helpful