In your attached workbook X & Y client is having only two values. Sales is not discrete here, means for X how can we find the top & bottom as there is only a single dimension & single measure. BTW, Use the below formula: -
if attr([Department]) == 'Furniture' then (first() == 0 or last() == 0)
else if attr([Department]) == 'Office Supplies' then
(first() == 0 or last() == 0 or first() == -1 or last() == 1) end end
Here replace [Department] with your client field. You also need to put the indexes for each sales entries so that it will show the discrete values. Put this field in filter selection & choose 'False'. Hope this may help you!
I've added a couple of sheets to your workbook - one giving just the answer you want and one showing some more fields to show how it works.
It's a bit crude and I haven't thought through how it will work if your clients don't happen to have a multiple of 10 sales records - but I think the "correct" answer is a bit ill-defined in that case anyway. If there are large numbers of sales per customer the impact of that is probably less important.
It basically works by counting how many rows there are with each value and omitting the first 10% and last 10%. It would be slightly easier if your data had a unique ID for each row, rather than having to generate an index on the discrete values and multiply by the number of records for each.
Have a look at the sheet [Workings] and see if it makes sense. Note in particular that the table calculations on the Measure Values shelf ([Index], [Rank] and [Sale_10_to_90]) have their "Compute Using" set to [Sale] - which means that there is a partition for each customer and the calculations look at all [Sale] values in each partition sorted by ascending [Sale] value.
The [Answer] sheet just shows the [Sale_10_to_90] field and filters down to a single row by just showing the row with [Index] = 1. Note that that filter is applied after all of the calculations have taken place because the filed formula is the Index() table calculation function and filters on table calculations have to be applied after the table calculation has been evaluated.
Thanks a lot Richard, it worked nicely.
You need to use "Measure Names" and "Measure Values" - special Tableau fields which allow you to do what you want. Have a read on them in the online help.
Updated workbook attached.
Actually, after posting that last response I noticed that my answers for the average weren't the same as yours. Thinking about it I realised that we were both wrong.
Your answer was giving the sum of all of the distinct values in the 10% to 90% range. Some of the values had 2 or 3 or 5 instances, so by taking the WINDOW_SUM() of the AVG() values you were effectively only counting each distinct value once (the AVG() just applied at the level of the distinct values).
My first answer was also wrong, because I took the WINDOW_AVG() of the distinct values - which means that I didn't take account of the number of instances of each value. For example, if the numbers had been 1, 1, 1, 2, that would have given a value of 1.5 (the average of 1 and 2) rather than 1.25 = (1+1+1+2) / 4.
The latest attachment has a correct version, calculated as the overall total divided by the overall number of records in the range.