7 Replies Latest reply on Jan 29, 2019 11:49 PM by christian.binderkrantz

    Column totals not summing up correctly

    john.garrigan.1

      Hi All,

       

      I am a little confused by how Tableau is working out the values in my table below but here's a summary of my problem.

       

      I have a table of values as per below,

       

       

      I have a separate table called member months which I would like to use as a denominator in my calculated field. Please note this data comes from a different data source than the table above.

       

       

      I have created a new table called PA requests per member month which should divide PA requests by the corresponding member month value.

       

      So for PA requests per member month for 2015 Q1 I should have 3 values in my table

       

      (37695/37300000)*12000 = 12.1

      (289596/37300000)*12000 = 93.2

      (327291/37300000)*12000 = 105.3

       

      The calculated field of PA Requests per MM for the table below is

       

      (SUM([Pa Requests])/SUM([MEMBER MONTHS].[Member Months]))*12000

       

      From the table below the first two rows are correct but the column totals are wrong, it appears Tableau is getting the average of the two rows rather than the sum.

       

      How can I correctly calculated the row total for this?

       

        • 1. Re: Column totals not summing up correctly
          Okechukwu Ossai

          How is the total calculated? Try switching it from 'Automatic' to 'Sum', if you have not already done so.

           

          Like below;

           

          • 2. Re: Column totals not summing up correctly
            john.garrigan.1

            Hi Okecchukwu,

             

            Unfortunately the SUM functionality is not available when I follow your instructions, only automatic is available.

             

            Is there another way I can do this?

             

            Thanks.

            • 3. Re: Column totals not summing up correctly
              christian.binderkrantz

              Hi John

               

              Try replacing your measure with this:

               

              SUM  ( { FIXED DATEPART('year', [Date Parse Quarter Month]),DATEPART('quarter', [Date Parse Quarter Month]),[Interaction Type (group)]: [PA Requests per MM] } )

               

              In case I misspelled a variable, you may need to edit it slightly.

               

              __

              EDIT:

              As Okechukwu has pointed out, this LOD calc is only possible when the data comes from the same data source.

               

               

              When you are using multiple data sources you might need a less 'elegant' solution.

               

               

              Try this for your measure and replace the fields accordingly:

               

              ZN( SUM( IF [Interaction Type] = 'ePA' THEN [Pa Requests] END )

                  /

              SUM( IF [MEMBER MONTHS].[Interaction Type] = 'non-ePA' THEN [MEMBER MONTHS].[Member Months] END ) )

              +

              ZN(SUM( IF [Interaction Type] = 'Office Supplies' THEN [Pa Requests] END )

                  /

              SUM( IF [MEMBER MONTHS].[Interaction Type] = 'Office Supplies' THEN [MEMBER MONTHS].[Member Months] END ) )

              +

              ZN(SUM( IF [Interaction Type] = 'Technology' THEN [Pa Requests] END )

                  /

              SUM( IF [MEMBER MONTHS].[Interaction Type] = 'Technology' THEN [MEMBER MONTHS].[Member Months] END ) )

               

               

              NOTE that above calc assumes you blend the data sources on [Interaction Type]

               

              If [Interaction Type (Group)] is acting up, you may need to create a calculated grouping (e.g. CASE [Interaction Type] WHEN  'a' THEN 'ePA' WHEN 'b' THEN 'ePA' WHEN 'c' THEN 'non-ePA' END)

              • 4. Re: Column totals not summing up correctly
                Okechukwu Ossai

                The SUM functionality is not there probably because the calculated fields are pre-aggregated. Totals can be a bit tricky depending on how your calculations have been set-up. Jonathan Drummey did a big post on it. Customizing Grand Totals – Part 1 | Drawing with Numbers

                 

                Try un-aggregating your calculations using LODs. However, it seems you are blending your data and it may cause some problems. An alternative is to calculate the total using a calculated field rather than adding it automatically.

                • 5. Re: Column totals not summing up correctly
                  christian.binderkrantz

                  If you are using multiple data sources then you may need a less 'elegant' solution.

                   

                  Try this for your measure and replace the fields accordingly:

                   

                  ZN( SUM( IF [Interaction Type] = 'ePA' THEN [Pa Requests] END )

                      /

                  SUM( IF [MEMBER MONTHS].[Interaction Type] = 'non-ePA' THEN [MEMBER MONTHS].[Member Months] END ) )

                  +

                  ZN(SUM( IF [Interaction Type] = 'Office Supplies' THEN [Pa Requests] END )

                      /

                  SUM( IF [MEMBER MONTHS].[Interaction Type] = 'Office Supplies' THEN [MEMBER MONTHS].[Member Months] END ) )

                  +

                  ZN(SUM( IF [Interaction Type] = 'Technology' THEN [Pa Requests] END )

                      /

                  SUM( IF [MEMBER MONTHS].[Interaction Type] = 'Technology' THEN [MEMBER MONTHS].[Member Months] END ) )

                   

                   

                  If [Interaction Type (Group)] is acting up, you may need to create a calculated grouping (e.g. CASE [Interaction Type] WHEN  'a' THEN '1' WHEN 'b' THEN '1' WHEN 'c' THEN '2' END)

                  • 6. Re: Column totals not summing up correctly
                    john.garrigan.1

                    Hi Christian,

                     

                    Thanks for the reply.

                     

                    It's probably not clear in my post but my member months variable is not broken out by interaction type the way the PA request variable is.

                     

                    I've slightly modified your calculated field to the following but similar to what I have the grand total is returning the average of the two columns.

                     

                    (ZN(SUM(IF[Interaction Type (group)] = 'ePA' THEN [Pa Requests] END))*12000

                    /

                    SUM([MEMBER MONTHS].[Member Months]))

                    +

                    (ZN(SUM(IF[Interaction Type (group)] = 'non-ePA' THEN [Pa Requests] END))*12000

                    /

                    SUM([MEMBER MONTHS].[Member Months]))

                    • 7. Re: Column totals not summing up correctly
                      christian.binderkrantz

                      Glad you could use the calculation, despite a block too much and some wrong DIM names from the Superstore sample data

                       

                      I am sure it has something to do with the level of detail. As previously mentioned, this is quite tricky to work around when using multiple data sources.

                      Without a sample workbook, I cannot help you further, I am afraid.

                       

                      If you end up figuring it out, please share your solution to the thread.

                       

                      //CB