9 Replies Latest reply on Aug 4, 2016 11:09 AM by Patrick T

# Level of detail calculation help

Hi,

I made a sample data set with three variables: date, Animal (type), number bought (value).

I am trying add percent of total bought to the tooltip (ie. chickens account of 37% of all animals bought for <date>). I would like the percentages to remain constant, if I filter an animal type out.

I have been trying to set up a level of detail calculation for the denominator, but I'm not having any luck

I tried: {FIXED [Date] : SUM([Number Bought)} but it returns the number bought for each animal type, exactly like SUM(Number Bought) gives

I tried: {FIXED : SUM([Number Bought])} but this gave me the total number of animals bought ever, without taking date into consideration.

I have attached a copy of the workbook. Maybe someone can lend a hand, and give an explanation of what I'm doing wrong? Thanks.

• ###### 1. Re: Level of detail calculation help

I use table calcs for that.  See attached.

Note that I tell [Total Per Quarter] to restart every quarter in Edit-Table-Calc

• ###### 2. Re: Level of detail calculation help

Ah.  Forgot to add in a way to filter out an animal without changing the percentages.

If you use a quick filter as you did, the physical rows from the data source are removed from the sheet's table, and the calcs change accordingly.

If you filter using a table calc, tableau keeps the underlying table intact, and just changes what is actually displayed on the sheet.

LOOKUP() is a table calc function.  See what I did in the attached example.

• ###### 3. Re: Level of detail calculation help

Thank you for your help, but your solution is not quite what I'm looking for. I would like for the percentages to stay static even if I filter an animal type out. For example, with all animals included, 2001 Q1, cow is 20.5% of all animals bought. However, if I filter out chickens, that percentage rises to 26.79%.

What I would like is for the percentage to stay at 20.5%, regardless of whether I filter out an animal type or not.

• ###### 4. Re: Level of detail calculation help

The workbook in the second reply does that.

• ###### 5. Re: Level of detail calculation help

Ok, this seems to work well. I'll try to apply to my actual work. In the meantime, I'll leave the question unanswered in case anyone else may have a different solution. Thanks

• ###### 6. Re: Level of detail calculation help

The solution for filtering out lines but not changing the percentages will definitely be a table calc filter.  That's how you accomplish that.

Someone might propose an LOD solution instead of table calcs though.

• ###### 7. Re: Level of detail calculation help

I made a big mistake in the creation of this question and neglected the fact that in my actual work the filter is another sheet. In the picture below, sheet 1 is the filter for sheet2. Is it possible to use your solution still?

• ###### 8. Re: Level of detail calculation help

When you do an action filter, the target sheet is impacted like a quick filter.  It will physically remove the non-selected rows.  It will act like your original [Animal] filter.

So in the attached, I created an LOD that sums by quarter.  You'll see that it arrives at the same quantity as the table calc.  However, you can see the behavior (in both the LOD and the table calc) when you use a quick filter.  All you get are totals of the data that remain after the filter is applied.

And you can't do "APPLY TO ALL" or "APPLY TO SELECTED" with a table calc.  The values are specific to the sheet.  Otherwise that would have been a possible approach here.

Also attached is a workbook I did for something else to demo a problem we were encountering.  It uses parameters to turn on/off individual lines in a chart.  Maybe you can use this technique to select which lines to "pass" to the target workbook.  The limitation here is that you will need one parameter per line.  Your test example has 4 animals.  That's not so bad.  But if your actual workbook has 100 choices, that's cumbersome.  (Ditto if choices will change from day to day.)

But if your data lends itself to this approach, you can make a table calc filter that grabs all the parameters and displays the ones that are "Y".

• ###### 9. Re: Level of detail calculation help

Thank you for all of your help.