1 2 Previous Next 18 Replies Latest reply on Mar 29, 2018 11:54 AM by Gerardo Varela

    How to sum sales by ranking 1,3,5...

    Linda Dawson

      Hi, I have a data sample:

       

      ID,Sales

      A,5

      B,6

      B,3

      C,2

      D,1

      D,6

      E,4

       

      I want to the grand total shows the sum of the sales by ranking 1,3,5 in descending order by ID. Here it should be 9+5+2=16.

       

      Thanks,

       

      Linda

        • 1. Re: How to sum sales by ranking 1,3,5...
          Joe Oppelt

          Are you familiar with the RANK function?  If so, create a calc

           

          RANK(SUM(Sales), 'desc' )

           

          Depending on how your sheet is laid out, you may need to tell that table calc to go TABLE(across) or TABLE(down).  That's going to depend on the layout of your sheet.

           

          Once you have that calc do:

           

          WINDOW_SUM( if [your RANK calc] = 1 or [your RANK calc = 3 or [your RANK calc] = 5 then SUM(Sales) END )

           

          And again, you may need to mess with the table calc setting on that calc as well.

          1 of 1 people found this helpful
          • 2. Re: How to sum sales by ranking 1,3,5...
            Linda Dawson

            Joe,

             

            Thanks for your reply. According to your solution, I got the results as the screenshot, which is not what I want. I want the result as:

             

            ID        Sum of 1,3,5

            A                   5

            B                   9

            C                   2

            D                   7

            E                   4

            Grand Total 16

             

            16 is the results of B,A and C, which ranks 1,3 and 5.

             

            Thanks,

             

            Linda

            • 3. Re: How to sum sales by ranking 1,3,5...
              Joe Oppelt

              Is there a way that you can upload the workbook you are working on?

               

              It's going to be a whole lot easier to show you what I want to discuss than to try to describe it.

               

              On the pill [Sum of 1,3,5] on the measures shelf you see a little triangle at the right side of the pill.  Click that and you can select "Edit Table Calc".  It's in here that you need to tell Tableau what direction (down or across) you want both RANK and SUMOF to evaluate.

              • 4. Re: How to sum sales by ranking 1,3,5...
                Linda Dawson

                Joe,

                 

                Please see the workbook attached. I have edited the table calculation to computing using ID.

                 

                Thanks,

                • 5. Re: How to sum sales by ranking 1,3,5...
                  Adriano Esper Kallas

                  Good afternoon. I'm sorry if my suggestion is not exactly what you asked, but how about you use the Total function we have in the left sidebar under Analyze> Summarize> Totals?

                  • 6. Re: How to sum sales by ranking 1,3,5...
                    Joe Oppelt

                    I see it now.

                     

                    You have WINDOW_MAX, not WINDOW_SUM in the [SUM of 1,3,5] calc.

                     

                    9 is the max.

                    • 7. Re: How to sum sales by ranking 1,3,5...
                      Linda Dawson

                      Joe,

                       

                      If window_sum, it is like this:

                       

                      Simply 16 instead of 9. It is also not what I want.

                       

                      Linda

                      • 8. Re: How to sum sales by ranking 1,3,5...
                        Joe Oppelt

                        One other thing.  You're not going to get the behavior you want from Tableau's "Grand Total" feature.  It sums up the SUM(Sales) measure OK, but it won't do what you want on the grand total of a table calc.

                         

                        Instead, create a second sheet to sum up 1,3,5 and position it on a dashboard where it looks right.


                        See attached.

                        1 of 1 people found this helpful
                        • 9. Re: How to sum sales by ranking 1,3,5...
                          Soham Wadekar

                          Hi Linda,

                           

                          I could come up with something like this .But as mentioned above by Joe , the grand total feature will not show 16.

                          • 10. Re: How to sum sales by ranking 1,3,5...
                            Linda Dawson

                            Joe,

                             

                            Thanks. It' helpful. I am disappointed that we cannot do this using grand total.

                             

                            I know that with ATTR, we can change row-level to aggregate. With LOD, we can change aggregate to row-level, Do we have a method to change table calcs to row-level alcs?

                             

                            Thanks,

                            • 11. Re: How to sum sales by ranking 1,3,5...
                              Joe Oppelt

                              Linda -- I missed this reply yesterday.

                               

                              Linda Dawson wrote:

                               

                              Joe,

                               

                              If window_sum, it is like this:

                               

                              Simply 16 instead of 9. It is also not what I want.

                               

                               

                              A table calc result actually ends up on every row in the table where it is engaged.  The sum you need is 16, but it shows up on every ID row.  That's why I did that extra filter on the third copy of the sheet.  Table calcs work on an index basis.  They only work on aggregates (or ATTR(), which is technically treated as an aggregate entity when it comes to table calcs.)  So having INDEX=1 just displays the first of the multiple copies of the value, and I used that as my own "totals" sheet (and I do this technique all the time in my own job.)

                               

                              The reason table calcs don't total up in the Grand Total feature is that the Grand Total line is treated as a special row and always has index=1.  I look at it as part of the machinery under the hood.

                              • 12. Re: How to sum sales by ranking 1,3,5...
                                Joe Oppelt

                                Linda Dawson wrote:

                                 

                                ...

                                 

                                I know that with ATTR, we can change row-level to aggregate. With LOD, we can change aggregate to row-level, Do we have a method to change table calcs to row-level alcs?

                                 

                                 

                                In many cases we can use LOD to replace table calcs.  But in this case we cannot.  There is no LOD equivalent of RANK.  Once the "factory" of calcs has a table calc introduced, everything that uses it will be treated as a table calc as well.


                                And in your case it requires RANK to find values 1,3 and 5.

                                 

                                Look at the first sheet in the attached.  I hacked up a calc that grabs SALES when ID = A, B or C.  this is a straight row-level calc.  Turn on TOTALS for this sheet and we'll get our 16 for that measure.  I made a new copy of this sheet.  See Sheet 4 and its subsequent copies.

                                 

                                The difference between Sheet 4 and Sheet 4(2) is that I made a calc that employs a common technique we use to control what goes into the total line.  When FIRST()=LAST() we know that we have a one-row "chunk" of data.  Most times this is the Grand Total row.  (If you only have one data row, FIRST() will also equal LAST() so there are limitations to this technique.)  Using FIRST() and LAST() makes this calc a table calc, and being able to control the Grand Total line this way makes me envision the Grand Total as a table calc value on its own.  Anyway, when first=last, we can tell the display calc to shove something else in there.  In this case I'm shoving the hack calc for A,B,C, and there it is:  We have 16 in the grand total.

                                 

                                Now go to Sheet 4(3).  I have added our actual table calc to TEXT.  Tableau is still honoring the behavior of [Display Sales], and we're getting that pesty 27 for the total of [Sum 1,2,3].  Now go to Sheet 4(4).


                                Here I made a copy of [DIsplay Sales] to insert [Sum 123] into the first=last (Grand Total) row.  But it's still shoving 27.  Even though we can see 16 for the row-level values, it's still calc-ing 27 in that grand total row.  It's a table calc of a table calc, and we just don't have control over tableau's special row like we do in the data rows.

                                 

                                So the root of the issue here is that we need RANK to identify 1,3,5.  If there were another way to identify it we might be able to employ the FIRST=LAST trick.

                                2 of 2 people found this helpful
                                • 13. Re: How to sum sales by ranking 1,3,5...
                                  Linda Dawson

                                  Joe,

                                   

                                  Thanks for the explanation in detail.  I am very grateful for your help.

                                   

                                  I suggest Tableau to develop the following two items:

                                   

                                  (1) Rank function without using Table calcs.

                                  (2) A method to change table calcs to row-level calcs.

                                   

                                  Linda

                                  • 14. Re: How to sum sales by ranking 1,3,5...
                                    Joe Oppelt

                                    There is an IDEAS forum where these sorts of suggestions get posted. 

                                     

                                    Ideas

                                     

                                    Search first to see if someone has already proposed each one.  (Do them in separate threads.)  If no proposal exists, start a new thread.  If you find one already out there, you can add an up-vote to it.

                                    1 2 Previous Next