11 Replies Latest reply on Feb 17, 2016 1:19 AM by Atiqur Sumon Sumon

    How to modify the Subtotal calculation

    Karen Le

      How can I calculate Subtotal that only using All Status & Goal and excluding the "Complete (Locked)" out of the calculation?

       

      It seem very simple but i am new to Tableau and i can't figure out how to do it - I read a lot of posts but still don't know how.

       

      Thank you in advance for your help!

        • 1. Re: How to modify the Subtotal calculation
          Tyler Garrett

          Wow that's a big cross tab! Looks like fun --- but it appears you're probably used to what EXCEL can do and wondering why it's not working that way!!!

           

          Because you're not able to "restrict" the aggregation done. Here's an alternative method to using the built in feature that TOTALS everything in the VIZ. Yeeeessss I realize summing profit and sales is silly .

           

          Note : I'm not SUM'ing the MIN(1000) because of this strategic usage of Tableau's calculated fields - just using it a bit 'outside of the box'.


          Let me know if you're still having trouble getting this to work!

           

           

          Cheers!

           

          Tyler Garrett

          VP of Solutions & Strategy

          TableauHelp.com LLC in Austin, TX

          1 of 1 people found this helpful
          • 2. Re: How to modify the Subtotal calculation
            Karen Le

            Hi Tyler,

             

            I see what you did there.  However your "Profit" and "Sales" are a measure in this case.  My "All Status.." and "Goal" are a record within "Project Status" Dimensions.

             

            1 of 1 people found this helpful
            • 3. Re: How to modify the Subtotal calculation
              Karen Le

              and please excuse me for my beginner usage of terms.

              1 of 1 people found this helpful
              • 4. Re: How to modify the Subtotal calculation
                Tyler Garrett

                No problem Karen.

                 

                This generally means you're not pulling from a "relational table" for that I'm sorry you will need to some extra work in Tableau. Opening a line of conversation with IT/IS team could be beneficial to have this data materialized in a normalized table VS making Tableau "work" with your data. Just an idea.

                 

                You're going to have to call out the dimension THEN (measure)

                 

                1 BIG things though. You've generated a "GROUP" you will not be able to use this in your calculation. You just found your first product limitation, this means you're really using the product!!! Congrats to be celebrated indeed.

                 

                SO lets pretend we are rewriting a group using REGION from super store.

                 

                If [region]='South' and [region]='East'

                then 'South East'

                elseif [region]='North'

                then 'North'

                end


                Now that you have regenerated your group (no big deal if you get stuck here)

                 

                You can use this previous calc - we will call it CALC1

                 

                sum(If [CALC1]='South East'

                then Sales

                else 0

                end)

                +

                sum(if [CALC1]="North")

                then Sales

                else 0

                end)

                 

                Let me know if this is starting to make sense!

                 

                -tylerG.

                2 of 2 people found this helpful
                • 5. Re: How to modify the Subtotal calculation
                  Tyler Garrett

                  So in your terms, after you change the group to a logical condition that you can call from, or you could simply find what "MAKES Goal" and then add it to your "IF condition"

                   

                  if [Project Status]='Goal'

                  then (aggregation)

                  else 0

                  end

                   

                  If doing the group in an IF statement is problematic - Pretend: Goal is (X+Y+Z) = (non group dimension)

                   

                  If [Project Status]='X' and [Project Status]='Y' and [Project Status]="Z"

                  Then (aggregation)

                  else 0

                  end

                  2 of 2 people found this helpful
                  • 6. Re: How to modify the Subtotal calculation
                    Karen Le

                    I follow your instructions and recreate the group but i got an error at step 2 when i try to combine the sum.

                     

                    ScreenShot2538.jpg

                    ScreenShot2539.jpg

                     

                    i also attached the file in case i didn't make sense.

                    1 of 1 people found this helpful
                    • 7. Re: How to modify the Subtotal calculation
                      Tyler Garrett

                      Professional guidance: I strongly recommend you delete the attachment from your previous post asap. This doesn't appear to be sample data.

                       

                      The reason you're having problems is because of the shape of the data, which IMO should be address first. Tableau works best with relational tables. Most of what I've suggested is taking that into account.

                      But if requirements say you must do it in Tableau, lets continue:

                      I think your best bet is to start using lookup() LOOKUP(expression, [offset])  and search around for a better write up on it. There are a TON of great blogs on it.

                       

                      Good luck and have fun! Sorry I won't be able to give you a direct answer because I fear I don't know enough information to give you the 100% correct button. I personally choose to solve my problems in the DATA, then use Tableau for the visual part. Hopefully my time here has empowered you enough to start working out your solution.

                       

                      Thanks,

                      Tyler

                      2 of 2 people found this helpful
                      • 8. Re: How to modify the Subtotal calculation
                        Karen Le

                        I appreciate your time and thank you for all your suggestions and advice.  It was very helpful still.

                        1 of 1 people found this helpful
                        • 9. Re: How to modify the Subtotal calculation
                          Tyler Garrett

                          Very welcome, happy to help. Tons of product expert in here and freakishly friendly vibes, enjoy the community!

                          1 of 1 people found this helpful
                          • 10. Re: How to modify the Subtotal calculation
                            Atiqur Sumon Sumon

                            I do not have the correct answer but

                            Karen Le sound is good may be he done your job .