You could just create a [# On Time] measure:
IF [Arrival Variance]<[Late (Min)]
THEN [Number of Records]
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!
OnTime.twbx.zip 20.0 KB
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!
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.
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
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.
That's brilliant, Alex! Very crafty.
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?