6 Replies Latest reply on Nov 9, 2012 7:00 AM by Brittany Fong

# Calculation with filtered measure and total number of records

I have a bar graph where I am computing % of orders on time.  I need to be able to exclude certain "reason late codes" (C1 and C2 because they stand for a weather delay or something out of the carriers control).  But still be finding the % of orders on time in relation to all orders (regardless of late code)

So right now when I use a filter Tableau is taking: (number of orders on time for E1 and E2)/(number of orders for E1 and E2) whereas I would like (number of orders on time for E1 and E2)/(total number of orders for total number of orders: E1, E2, C1, and C2).  Is there a way that I can store the total number of records and use it in a calculation?

I have attached a workbook bc I know this problem sounds very confusing.

Thanks in advance!

• ###### 1. Re: Calculation with filtered measure and total number of records

Hi Brittany,

You could just create a [# On Time] measure:
IF [Arrival Variance]<[Late (Min)]
THEN [Number of Records]
ELSE 0
END

Then create one more calculated field which will give the % and put it on your view:
sum([# On Time])  / sum(Number of Records)

Hope this helps!

1 of 1 people found this helpful
• ###### 2. Re: Calculation with filtered measure and total number of records

That does help.  I was trying to figure that out earlier.  But I still need a way around the filter part so that when it divides by the number of records it is all records not just ones in the filter.  Thanks for the help though since that was a question I had earlier!

• ###### 3. Re: Calculation with filtered measure and total number of records

Ahh. Well, as you filter out items, you are removing them from the data set. You are saying you want to have one measure be unaffected by the filter, where the other would be affected. To do that, I think you'd need to duplicate your data source... pull the filtered measure from the filtered source and pull the unfilterable measure (total) from the secondary source.

• ###### 4. Re: Calculation with filtered measure and total number of records

We can avoid a duplicate set by using a hack of sorts. If we use a table calculation to filter, the filtering occurs in Tableau rather than in the source.The calc for the filter is

lookup([late codes],0)

This is a table calc that returns the value of the late codes from the same row - i.e. does nothing.

Now if we use total(sum(# of recs)) the total sum is based on the entire dataset, even the filtered items.

• ###### 5. Re: Calculation with filtered measure and total number of records

That's brilliant, Alex! Very crafty.

• ###### 6. Re: Calculation with filtered measure and total number of records

I tried to create the lookup([Late Reason Code],0) however the late reason code isn't a number its a string so I keep getting an error that it need to be an agg.

I saw in another forum/article where someone use lookup(min([Late Reason Code],0) however that is only returning 1 reason code.

Am I missing something?