See if this works for you. You can't filter on type, and you needed to average your lat/long.

You are correct in how it works. You could make this view much more flexible if you transformed your data to have only one record per customer. That way you wouldn't need table calculations. With table calculations, you have to specify the "Compute Using" to account for all the relevant fields in your view. So if you add pills to your view, you have to manually fix the "Compute Using". You might be able to conceive of a way around this by using parameters, but I think you'd just be making it unnecessarily complex. I say, just fix your data.

What I mean is that your base and target values are distinct columns for each customer instead of creating two records per customer. Blending the data might be another option that could work if the suggestion to reshape your data isn't practical. You could have a base and a target data set.

Now that I think about it more, you still will need to use table calculations. So I don't think you are going to be able to create the flexibility that you want for the reasons I stated above.

Hi Justin,

You wrote:

I want to be able to add any additional dimensions to the view without changing the outcome of the formula.

This is not how table calculations work in Tableau. With table calculations, we set the Compute Using dimension(s) that define the addressing, i.e. what combinations of values we will calculate results for. All other dimensions in the view*** are automatically part of partitioning, i.e. the combinations of values that will cause the calc to "start over". My current understanding is that Tableau works this way to support the cycle of visual analysis: as we can drag and drop dimensions on the view to create new panes, Tableau will compute as we'd naturally expect for each pane created.

Also, depending on where dimension pills are in the view, you can trigger densification behavior. That's part of what is going on in your "not so successful" crosstab view, by putting Product on Columns and Customer Segment on Rows, then there is a cell for every combination for Tableau to compute the table calcs over.

*** "In the view" means dimensions on the Marks Card, Rows, Columns, and Pages Shelves. Dimensions on the Filters Shelf are not considered for addressing or partitioning in table calculations.

It's also important to know that row-level filters and filters on aggregates are applied before table calculations are computed. I'm pretty sure whether a single filter is context or not will have no impact on table calc results (except perhaps to improve performance), though of course in combination with other filters context filters can have a tremendous impact. If you want certain results to remain the same no matter what filters are chosen, there are a few ways to do this in Tableau:

- Use Custom SQL or query to generate a total outside of Tableau so that it is available to every row, no matter what is filtered.

- Use a data blend with custom relationships that will ignore the filters to get the total.

- Use table calculation filters, which are applied after most all other computations are completed.

I haven't gone through this in complete detail, but as a general rule what Mark said about flattening the data and combining the base and target values is useful. Also, keeping the number of dimensions to a minimum is useful, wherever possible use aggregates, like Mark set up. Once a viz uses table calcs, further manipulation of the viz gets more complicated. Sometimes a little, sometimes a whole lot, this seems to be one of the latter. For this kind of view, I use the kind of thinking I describe in http://drawingwithnumbers.artisart.org/eddie-van-halen-and-dashed-lines/:

1. Understand your goal.
2. Understand your data.
3. Figure out what the ideal data structure and values – given an understanding of how Tableau works – would look like to reach that goal.
4. Then and only then figuring out what calculated fields, dimensions, measures, and/or reshaped data would lead back to #1.
5. Build the view. Now you start dragging and dropping pills.

Here's a starter: Ultimately, you want one dot per customer, so that means you're going to want the Customer ID in the view. There's only one Customer Segment, Latitude, and Longitude per Customer, so all of those can be aggregate fields and not affect your partitioning. However, you didn't specify what you wanted the totals to be computed over when Product is added to the view, so I'm not able to help you out there.

Jonathan

Hi Justin,

So I understand why the formula broke on the "notsomuch" tab, what I'm trying to figure out is how I can structure the formula/pills such that the result comes out the same in spite of the new layout. At the surface level, I was trying to ascertain if there is a trick that can be used for a table calculation to kind of ignore the partitioning, or somehow go around it. The formula should calculate along customer segment no matter the view.

You don't need a different calc for each view, that's part of the beauty of Tableau. Instead, you have different Compute Using's for the table calculations for each set of dimensions used in each worksheet. When there are more dimensions to be used for addressing that's where the Advanced... Compute Using comes into play (along with Restarting Every and At the Level should you need them). You can add the additional dimensions to right side of the Advanced... Compute Using dialog and that adds them to the addressing. Depending on the calc, this may cause extra results of the same value to appear such as for a WINDOW_SUM() calc, or create new values like for an INDEX() calc.

I'm not sure what you mean by "The formula should calculate along Customer Segment," it seems from my investigation that it doesn't matter since there's only one segment per customer, is that the case?

Jonathan

Ok, here's where I think you were getting tripped up:

1. When your goal is a value per Segment, the Segment is partitioning, not addressing. This can be confusing in Tableau, we often think of partitioning first but the way we control it is through seeting the addressing/Compute Using.

2. While you did an initial validation in a crosstab view, the later views that had more dimensions in the view did not have their own crosstabs. This makes figuring out what is going on with table calcs a lot more difficult. My suggestion is to build a view where all your dimensions are on Rows, with the right-most dimension(s) being what is used for the Compute Using. That makes it really easy to see if you're getting the right values, with many calculations you can turn on totals & subtotals for validation.

3. While SUM(target volumetric) works fine as the numerator when there's only Segment in the view, when you add additional dimensions to the view it fails because the view now has a finer level of granularity; alternatively, one could say there are more (smaller) panes. The solution to that is to build two table calcs, for example in the "workout" crosstab in the attached I set up a [Tgt % of Profile] as  TOTAL([Target Volumetric Sum])/[Tgt % of Profile Denominator], where the latter calc is also TOTAL([Target Volumetric Sum]). Tgt % of Profile Denominator has a Compute Using of Segment, Customer ID while Tgt % of Profile has a nested Compute Using of Customer ID. That way the Denominator calc generates a total across all Customers/Segments, and the Tgt % of Profile calc uses that value while calculating it's own total within each Segment. I built corresponding calcs for the Base % of Profile, and revised versions of the other calcs in the view, including a table calc for the filter.

I chose TOTAL here because the table calcs didn't require any sorting and in v7 TOTAL doesn't need the optimization techniques for performance that WINDOW_SUM does.

Hope this helps!

Jonathan

You're welcome! When I started working with measure data with numerators & denominators that tripped me up, too.

This makes a great example of how understanding the level of detail is necessary for correctly partitioning calculations, it's definitely worth coming back to!

Jonathan