2 Replies Latest reply on Apr 30, 2013 9:31 AM by Chuck McGlynn

# Is there a way to assign conditional calculations based on filters

I'm not really sure if the title explains what I need, but here goes...

I am creating a dashboard for the commercial properties (malls) that my company owns.  At these properties, there are multiple tenants.  Some of these different tenants roll up to the same parent company.  For example, "Hollister" and "abercrombie" tenants would both roll up to the "Abercrombie & Fitch Co." parent company.

I need to be able to view averages (such as average square footage) by tenant AND by parent company across all properties.  I am using a filter to view either tenant or parent company; the tenant calculations seem to be working fine. The parent company is where I am having the problem. When there are multiple tenants at the same mall who share a parent company, the tenants square footages are summed, then averaged.  For example, lets say there are 5 malls that have tenants under the "Abercrombie & Fitch Co." parent company.  One of the malls has both Hollister and abercrombie as tenants (so this would be 6 tenants total). Instead of averaging all 6 tenants' square footage, it is averaging all the parent company's tenants' square footage at the malls individually, then averaging them again (it is averaging using the number of malls, not number of tenants).

I am computing the calculations across the mall level, so it makes sense why it is doing this.   The tenant filter works because we don't have the same tenant at the same mall (there aren't two Hollisters at one mall).  I need the calculation to work both ways, by tenant and by parent company.  Is there a way to accomplish what I am looking for?

• ###### 1. Re: Is there a way to assign conditional calculations based on filters

Hi Chuck,

I think I understand your problem, but it'd be much easier if you could share a packaged workbook. If you have sensitive data, perhaps you could try to recreate your issue using some of the dummy data sets that Tableau provides out of the box. (click the little house icon tab near the top right and start from one of the Saved data sources).

It sounds like what you're really after is a parameter + calculated field combination. I employ this combination all the time.  Assuming you have a field that provides the Tenant name and another that provides the Parent name, the setup is as follows:

1) Create a string Parameter titled "Parent or Tenant" with List options of "Show by Tenant" and "Show  by Parent"

2) Create a calculated dimension field named  "View By":

IF [Parent or Tenant] = "Show by Tenant"
THEN [Tenant Name Field]
ELSE [Parent Name Field]
END

3) Put your new "View By" field on your view in lieu of the Parent or Tenant and then expose your parameter to allow the user to toggle which dimension occupies the space.

1 of 1 people found this helpful
• ###### 2. Re: Is there a way to assign conditional calculations based on filters

Thanks Mark,

I already had the parameter + calculated field combination set up almost exactly as you described, but it still didnt solve the issue.

I was able to solve the problem by making the original worksheet into two worksheets, then just combining them on the dashboard.  It doesn't look as pretty, but its functional and accurate.  I think the problem was cause by the many different filters and levels of detail I had on the original worksheet. Some of the levels of detail weren't required for all calculations on the original worksheet, so it was calculating some things at a different levels.