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

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

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...

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

 Ad Spend U.S. Registrations Car Model Car Segment Ad Spend % - Segment Registrations % - Segment Ad Spend % / Reg % \$100 4,000 Nissan Altima Family Sedan 22% 62% 0.361 \$300 1,500 Nissan Z Sports 60% 43% 1.400 \$350 2,500 Ford Focus Family Sedan 78% 38% 2.022 \$200 2,000 Ford Mustang Sports 40% 57% 0.700

Thank you again for any help!

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

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) ?

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) ?

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) ?

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) ?

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.

"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) ?

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!