5 Replies Latest reply on Mar 4, 2014 12:17 PM by Phillip Burger

    Sum not being calculated by Tableau

    Anuj Bajaj

      Hi guys,


      Please see the attached workbook.


      Dash 1 sheet

      Problem -  - On USA - Tristate region selection, you will see two values underneath as shown below.


      Current RegionScenarioMin. LatitudeMin. LongitudeCurrent or moves
      USA - TristateBentley40.72-74.16-764
      USA - TristateCurrent40.72-74.162,785


      I want the tooltip to show the sum as 2021 (2,785 + (-764)), but it shows up 2,785. Also, I don't want to convert the Circle marks to Pie.


      Dash 2 sheet

      I want to display the total count for each manufacturer(GM, Ford etc) without decimals. [I am new to tableau, I couldn't figure this out]


      Thanks in advance for your help.



        • 1. Re: Sum not being calculated by Tableau
          Phillip Burger

          On the Map worksheet, try changing the Scenario dimension on the Marks Card from a dimension to an Attribute. I don't entirely understand the effect this has in this worksheet and hope that a more advanced Community member can provide input. I looked at the data and the 2,021 is correct.


          Still on the Map worksheet, your marks are circles and not pie and so I'm not sure what I don't want to convert the Circle marks to Pie is in reference to.


          Re the formatting of the count, the best that I can interpret from your question is that you are referring to the Headcount value? If so, go to the worksheet Count by Region. Right-click in the pane and select the Format option. The Format Window should now be visible on the left side of the workspace.  Select Fields, and then select the SUM(Current or Final) field. Then select the Pane tab. Go into Default, Numbers:, Number (Custom), and change the decimal places from 2 to 0.


          If a member of the Community can explain and make sense for us the behavior differences of Dimension vs. Attribute on Marks Card, it'd be much appreciated! :-)

          • 2. Re: Sum not being calculated by Tableau
            Anuj Bajaj

            Thanks Phillip!!


            I have couple more questions.

            Dash 1 Sheet

            1. Default Filter Selection: I want to set 2013 check-box as default and don't want to allow the user to change it. Even after user selects "All" and then un-checks "All", the year 2013 check-box should remain checked/selected. Is that possible in tableau?


            Dash 2 Sheet

            1. Totals for stacked bar chart: When the user selects chrysler and GMC, the bar becomes a stacked bar chart and shows individual count for chrysler and GMC by region. Is there a way to show the totals for each region? for e.g. GMC - India count = 109

                    Chrysler - India count = 11

            I'd like the chart to show the total for India as 120.


            Thanks and appreciate your help.

            • 3. Re: Re: Sum not being calculated by Tableau
              Phillip Burger

              Re 1: I first created a paramter called year2013Hardcoded and assigned it a hardcoded value of 2013. If there is a systematic way to know which year you want them to look at, you can use a calculate the value in a calculated field and then use the value of the calculated field for the value of the parameter.


              Next, I added a condition on the [Year] dimension. In the general option, All is selected. Then in the Condition tab of the filter, a formula is added. The formula is a Boolean expression that evaluates to true or false. If the value of the Boolean expression is false, the record is filtered. I'd also remove the filter from the Dashboard. If you want to show the client the year they are viewing data for, you can add the parameter year2013Hardcoded to Marks card to make it available in a tooltip. Then go into the Worksheet > Tooltip and add the year information to the tooltip.


              I looked at the data in some filters and groupings in Excel to double this and it appears to have the intended behavior.


              There might be other ways to do this but this is where I'd start. I think it's a reasonably good way to prevent your clients from changing the year.


              Re 2: My explanation here might only be a partial solution. I removed the gray, Multiple Fields pill from the Color button on the Marks card. I think it can be kind of hard to control the colors on complex worksheets. If this were my worksheet and I wasn't able to obtain the control I wanted, I'd start a new worksheet and build it up in a way that is different than how the existing worksheet is built up.

              • 4. Re: Re: Sum not being calculated by Tableau
                Anuj Bajaj

                Thanks Philip for the quick response.


                Couple of questions on Dash 1 Sheet.


                1. You mentioned that I should remove the year filter from the dashboard, if I do so how would I select 2014 and 2015 years? The way you did it, I can only view 2013 count only.
                2. I didn't mention this in my previous post, but I want to create the same functionality for the scenario filter too. There I'd like the "Current" to be selected all the time (default).


                I'll try creating the Dash 2 sheet again and will let you know how it goes.


                Thanks again!



                • 5. Re: Re: Re: Sum not being calculated by Tableau
                  Phillip Burger

                  Ah.  I read I want to set 2013 check-box as default and don't want to allow the user to change it differently than what you intended. If you want 2013 to be the initial value and allow clients to update it, I'd keep the filter for Year on the worksheet but use a Single Value (Slider).  Set the value to 2013 and save the workbook. When you publish or share your workbook, the Dash 1 will display the values corresponding to the year 2013. The parameter isn't needed and you could use the filter as you originally have it in your attached workbook.


                  Unless it makes sense to sum of the measure for all the values of Year, go into the filter legend, select Customize, Show "All" Value, and uncheck Show "All" Value.


                  The approach for initializing the value of Current for the Scenario would be the same as described above. Here, it would seem to make sense to view the sum of the values for All values for Scenario.


                  I'm including the attached workbook as a convenience so you can see how the Year filter is configured.