5 Replies Latest reply on Jan 21, 2016 8:34 AM by Jeff Passen

    Sum Revenue on Filtered Product

    Jeff Passen

      I have a basic cross-tab where I have Geographical Area on the Y-axis and Year-Qtr on the X-axis.  I have Product on the X-axis in which I have chosen 3 products to find the Revenue and Standard Margin for across each Year-Qtr.  Similar to what is shown below.  How do I get total revenue for each product?  The products shown (A, B, & C) are filtered selections from the 'Product' column.  I show the Excel answer under the Grand Total columns to give a more clear description of what I am looking to do in Tableau.

      1Q142Q143Q144Q14Grand Total
      GEORegionProd. AProd. BProd. CProd. AProd. BProd. CProd. AProd. BProd. CProd. AProd. BProd. CProd. AProd. BProd. C
      RevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. MarRevStd. Mar
      AmericasNorth$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V5+P5+J5+D5)=SUM(X5+R5+L5+F5)
      South$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V6+P6+J6+D6)=SUM(X6+R6+L6+F6)
      East$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V7+P7+J7+D7)=SUM(X7+R7+L7+F7)
      West$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V8+P8+J8+D8)=SUM(X8+R8+L8+F8)
      APJAsia Pacific$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V9+P9+J9+D9)=SUM(X9+R9+L9+F9)
      Australia$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V10+P10+J10+D10)=SUM(X10+R10+L10+F10)
      China$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V11+P11+J11+D11)=SUM(X11+R11+L11+F11)
      India$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V12+P12+J12+D12)=SUM(X12+R12+L12+F12)
      EMEAEuorpe East$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V13+P13+J13+D13)=SUM(X13+R13+L13+F13)
      Europe West$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V14+P14+J14+D14)=SUM(X14+R14+L14+F14)
      Italy$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%$$%=SUM(V15+P15+J15+D15)=SUM(X15+R15+L15+F15)
        • 1. Re: Sum Revenue on Filtered Product
          Rachael Otto

          Hey Jeff,

          Are the products all members of the same field? If so, maybe try a calculated field like the following:

          If [Product] = "A" then Sum([Revenue])

           

          Also if the view isn't filtered you might try Grand Totals for columns, but be aware GT sums the underlying data, not the data in the view, so it can be confusing to work with.

           

          It is worth noting that thinking about data in terms of cells isn't the best way to work with Tableau. Tableau does things thinking in terms of fields, you add fields to the view and it does a lot on the back end to structure it in a way the software thinks makes sense.

           

          If possible, sharing the workbook would make this question easier to answer.

          • 2. Re: Sum Revenue on Filtered Product
            Jeff Passen

            Thanks for getting back to me.

             

            Yes, the products are all members of the same field.  I’ve tried the If statement you suggested but the calculation doesn’t verify: “cannot mix aggregate and non-aggregate comparisons or results in “IF” expressions”.

             

            Unfortunately I cannot send the workbook because it has confidential company data.

             

            Just so I’m clear about your last statement.  If I add a Grand Total to my data, it will total ALL of the values, not just the values displayed?  For example, If I have Products A, B, C, D, E & F but I filter on just A,B & C, the Grand total will be a total of A, B, C, D, E & F?  If so, that seems counter intuitive.  What if I wanted to show only the totals of the filtered products?

             

            Thanks, Jeff

            • 3. Re: Sum Revenue on Filtered Product
              Rachael Otto

              Hey Jeff,

               

              Sorry I realize my statement about grand totals was confusing. GT does use the underlying data to do its calculations and in concert with certain other operations this can return unexpected results. If simply filtering such as with the example you gave, GT should work as expected. However if you are also using table calculations in the view, or other operations, then the grand totals will be calculated before the table calculation is applied, which can return a confusing result. The following forum post provides a really detailed explanation of how grand totals work: Why Your Grand Total or Subtotal Isn't Working as Expected

               

              If you run into issues with Grand Totals, there are a lot of resources to help, but if its not an issue with the view you're building then I apologize for being confusing.

               

              As for the calculation error for aggregate vs. non-aggregate comparisons, try wrapping [Products] in a MIN() or ATTR() function.

               

              Hope that helps!

              • 4. Re: Sum Revenue on Filtered Product
                Tausif Mohammed

                Hi Jeff,

                 

                I ran into similar issue recently with grand totals where it doesn't seem to sum what's visible in the view (due to reasons provided by Rachel) & had to put a workaround. You might get some ideas to solve your problem. You can check it out on the link provided.

                Yet another Grand Total Query

                 

                Thanks,

                Tausif

                • 5. Re: Sum Revenue on Filtered Product
                  Jeff Passen

                  I think the original question has gotten lost.  I'm okay with the Grand Totals.  What I am trying to figure out is how to total the revenue by product over all years selected (see original post with the format of the data).  I've tried wrapping [Product] with both MIN() and ATTR() but I still receive the same errors: “cannot mix aggregate and non-aggregate comparisons or results in “IF” expressions”

                   

                  Any help on this would be greatly appreciated.