6 Replies Latest reply on Jun 12, 2013 6:58 AM by Andy Paolino

    Summing (and calculating ratios) by Certain Variables (SumIfs) ?

    Andy Paolino

      Hi everyone!

       

      I am rather new to tableau and am running into a major issue making a calculation within my dashboard.  I need to be able to calculate on the fly, based on certain filters, a metric that relies on summing by certain dimensions.  That is certainly a mouth full, so let me iron out an example below using automobile advertising spending and car registrations.

       

      This is a simplified example of what I actually need to do, so to accomplish my actual task I need to be able to calculate the 3 columns on the right within Tableau.  I am able to SHOW "Ad Spend % - Segment" and "Registrations % - Segment" using the "Table Calculation- % of Total" view in Tableau, but it seems like this simply changes the format/view of the numbers, and does not calculate the actual %s (that is, the "Ad Spend" and "Ad Spend % - Segment" would contain the same exact values, they would just be "formatted" differently when one is showing the table calculation).  The reason I need to calculate the actual %s as values in columns is because I need to then carry over to the last field, "Ad Spend % / Reg %," and show this ratio.  I would be able to do this in Excel, so I have to imagine it can be done in Tableau as well!  My Excel formula fors for "Ad Spend % - Segment" and "Registrations % - Segment" are below.  Any help is EXTREMELY appreciated!

       

      Excel formulas...

      "Ad Spend % - Segment": =$A2/SUMIFS(ad_spend,car_segment,$D2)

      "Registrations % - Segment": =$B2/SUMIFS(us_reg,car_segment,$D2)

       

      Ad SpendU.S. RegistrationsCar ModelCar SegmentAd Spend % - SegmentRegistrations % - SegmentAd Spend % / Reg %
      $1004,000Nissan AltimaFamily Sedan22%62%0.361
      $3001,500Nissan ZSports60%43%1.400
      $3502,500Ford FocusFamily Sedan78%38%2.022
      $2002,000Ford MustangSports40%57%0.700

       

      Thank you again for any help!

        • 1. Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
          Mark Holtz

          Andy,

          That should certainly be possible, but to best assist you, can you attach a packaged workbook (.twbx)?

          If your data is too sensitive, you can recreate your situation using the dummy data sets provided with Tableau.

          • 2. Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
            Andy Paolino

            Hi Mark,

             

            Thanks for the response!  I have attached a workbook to the original post that uses example data.  Essentially, is it possible to calculate the sales % and  cost % for each city, but by state, and show those ratios as two fields in adjacent columns?  And then, on top of that, create a field for sales % / cost %?  The denominator of each field (sales % and cost %) would have to be the total sales/cost for that state over the given time period selected in the date slider, where the numerator would be the sales/cost for the specific city over that time.

             

            Thanks so much for the help (sorry I'm such a newbie here, but I promise I learn fast)!

            • 3. Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
              Mark Holtz

              Hi Andy,

               

              You need to package the workbook. The version you included is a .twb and therefore does not have your data bundled together with it.

               

              Packaged Workbooks

              • 4. Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
                Andy Paolino

                Hey Mark, I have changed it to include the data.  Thanks and sorry about that!

                • 5. Re: Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
                  Mark Holtz

                  Hi Andy,

                   

                  Basically, when you want to involve data from "other rows" in Tableau, you are going to be using Table Calculations.

                  Table Calculations depend on which fields from your data you bring into your view to create "partitions" and calculate.

                  I found Table Calculations to be one of the more confusing tools in Tableau when starting, but you always have to remember that Tableau has access to the data at your underlying data-row level. In Excel, we often create data from multiple rows, which can be replicated in Tableau, but keeping that consideration in mind always made it easier for me to understand what I was trying to do.

                   

                  I'll break down your question:

                  "Essentially is it possible to calculate the sales % and cost % for each city":

                  Certainly. You technically want to partition your view by State, then City. You want to sum the total sales (and total same for cost) by state to use as your denominator. You can do this with a calculated field of: WINDOW_SUM(SUM([Sales])).

                  You will notice that as soon as you enter that formula, you will see "Default Table Calculation" appear above the text box to the upper right.

                   

                  If you click that, you can choose the field(s) to "Compute using:". You will select Advanced at the bottom because you need to use 2 fields. You must add State, THEN City to the Addressing section. The order matters. You will calculate "at the level" of City "restarting every" State. You can use "Deepest" for "At the Level"

                   

                  Now, you have access to State-level data on each row, and you can thus calculate the % of State total for each city.

                  You can continue adding to the same calculated field, but for illustration purposes, and troubleshooting, I often build the "incremental steps" in separate fields.

                   

                  I created a 2nd calculated field as "City Sales as % of State":

                  SUM([Sales]) / [State Sales Denominator]

                  You must use SUM() to aggregate sales because State Sales Denominator is an aggregate field (we used WINDOW_SUM on it).  Format as % and you have the % of Sales for each City.

                  Repeat for Cost.

                   

                  "show these ratios as two fields in adjacent columns"

                  Now you can add both of these fields to your view using the special measure values pill as you had in the table.

                   

                  "And then on top of that, create a field for sales % / cost %:

                  You can now create an additional calculated field as [City Sales as % of State] / [City Cost as % of State]

                   

                  I think that was everything you were asking for. If you have additional questions, I'd be happy to help.

                  • 6. Re: Re: Summing (and calculating ratios) by Certain Variables (SumIfs) ?
                    Andy Paolino

                    Hi Mark,

                     

                    This worked for me, thanks!  I wasn't able to actually open up the workbook though because I have Tableau 7.  Would you mind saving your workbook in Tableau 7 format and reuploading so I could check it out if needed for future reference?  I would be very grateful!  Thanks!