7 Replies Latest reply on May 4, 2012 10:13 AM by John Liska

# Calculating a percent based off of a targeted row value

Hi everyone,

I was wondering if there is a way to create a calculation that captures the % of a specific row value. This is difficult to describe so I've attached some example data in Excel.

The goal is to understand how the sales of a number of products stack up against the sales of one particular product in a set. As you can see in the Excel file example the goal is to understand how products 1, 2, and 4 compare to the sales of product 3. As it is laid out in the example I'm seeing the totals and percentages by geography. However, if the user chooses to look at all regions at once then the SUM values for product 3 will be different, and subsequently the "% of Targeted Sales Vol" for each of the products 1, 2, and 4 will also be different. Therefore the calculation must be dynamic: it would still anchor on product 3 but the percentages would be based off of the new totals at the aggregate level of "All geographies".

Unfortunately I'm new to Tableau so these types of calcs are fairly challenging for me. Thanks in advance for any insights.

• ###### 1. Re: Calculating a percent based off of a targeted row value

You may find the method here useful: http://community.tableau.com/thread/117765

Have a look at that, then we can work from there

• ###### 2. Re: Calculating a percent based off of a targeted row value

Alex,

Thanks for the quick (and useful) reply. Although it doesn't exactly solve the issue I'm facing it gives me an idea for another problem I'm working through.

My original example was not completely clear so I've created a new one. Because I don't know how many products are in a set or how they will be indexed I won't be able to use the lookup() and first() logic you pointed out. I will know what my "anchor" products are because they will all be marked. (In my new example this is column C.) If I'm able to potentially propagate the anchor volume out into a calculated field (in my example: column E) then they final calc is really easy. And this allow the final % calculation to work dynamically at different levels of aggregation which is one of my goals.

Let me know what you think. Thanks.

• ###### 3. Re: Calculating a percent based off of a targeted row value

Only one anchor SKU for each geography?

If so, you can still use lookup because we can use AnchorSKU as the ordering field.

It turns out that you can only sort a boolean field by count, but count somehow works - pushing true to last() 1 of 1 people found this helpful
• ###### 4. Re: Calculating a percent based off of a targeted row value

Workbook....

• ###### 5. Re: Calculating a percent based off of a targeted row value

Great stuff, thanks Alex. However, to answer your first question: no, there may be more than one anchor SKU. As such for each roll-up (in this case, geography) it would be the SUM of volume for the anchor SKUs. I created a new example Excel file which includes a Northeast geography which shows what I mean.

Sorry for the confusion. In my effort to make the example relatively easy to understand I've not included all the relevant details.

• ###### 6. Re: Calculating a percent based off of a targeted row value

Okay, in this case we have a window_sum of anchor SKU values across the partition. In this case it's set to geography. You would have to change the [percent] partitioning if you had another dimension you wanted to compare across.

• ###### 7. Re: Calculating a percent based off of a targeted row value

That ... is ... frickin' ... awesome. Thanks Alex!! Very much appreciated.