If you change your filters to be "context" filters (click on the dimension in the filter and you'll see an option "add to context"...it will also go gray/brown to indicate the filter is now "in context"). Top N filters are applied before any regular filters are applied, but adding a filter to context bumps it up the calculation pipeline, so it gets applied before the Top N is computed...if you wondered why this happens)
hope that helps
Here you go. I've added all the filters (except the one with the Top N) to context. Now one thing I noticed is that your %age of Total calculation has changed. This is because it's a FIXED LoD, which like Top N are also computed before the regular filtering, so the effect we get is that the FIXED LoD that makes up the "Total" (denominator) is now no longer the global total. Either use the solution suggested by Meenu (where by using a table calculation filter you can still access "all" the data), or possibly hard code that "total" value in the data source (I noticed a bit of custom SQL when I opened the file, so it might be an option...it's all the FIXED LoD is doing anyway!!)
Hope that helps
Thanks Simon! My mistake were that I have added all the filters to context, not excluded one with the Top N.
The problem right now is that I lost the 'Distribution of status' by LoD.
What do you suggest me to fix them?
So to do this we'll have to use a different technique. In the same way the LoD "Exploited" the way that FIXED LoDs are computed before any (regular) filter is applied, we can exploit the way that Table Calculation Filters are applied after filtering (this has the affect of "hiding" the unwanted rows, but still gives us access to the underlying data.
So first we re-create the %age Calculation using a table calculation
[% shipment por status_TC]
Then we can use the RANK function to RANK the entries (I've used RANK_UNIQUE so you always get back 32 rows, even if 2, or more, are tied)
and then we can use this as a Table Calculation Filter
We could have done the last 2 in a single calculation, but I've split it out to help explain what's going on.
As the Table Calc filter is applied last, the %age Calculation is still over all the data (that hasn't been filtered out by the other filters).
Hope that does the trick and makes sense
OK so this gets a little more complicated! The reason this is happening is that [SHP_SUBSTATUS_ID] is now in the VizLoD (Viz Level of Detail) and so the RANK_UNIQUE is run over both [SHP_SUBSTATUS_ID] and [agencia_origen]
So we're going to have to use a slightly different trick. Tableau sorts "headers" in ascending order (just naturally) so the negative of the SUM of [agencia_origen] will order things in descending order. Now we need to use a WINDOW_SUM so we can SUM over the [SHP_SUBSTATUS_ID] and return a single value for each [agencia_origen], so we create
Set up with the following set up
and this creates this
Now we have them in the right order, we can use index() to act as our rank
[Qty_Shipment_Index (AS RANK)]
we set this up like this
Then we can create the < Filter and set to true (on the filter)
[Qty_Shipment_Index (AS RANK)]<=[Top N]
Hope that makes (some) sense!
Cool...glad it worked.
Yes just untick the "show header" option in the pill (just click on the pill and you'll see the "show header" ticked, just untick it). I really just left them visible so you could see what it was all doing.
Thank you so so much for your help!!