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.
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)!
Sumifs Workbook.twbx.zip 1.1 MB
Hey Mark, I have changed it to include the data. Thanks and sorry about that!
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.
Sumifs Workbook.twbx.zip 1.2 MB
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!