The specified item was not found. had linked to this thread at Re: LOD from multiple Sources?, I decided to respond here.
@Alexander, the solution you were working on avoids one potential issue with table calculations & grand totals (namely that grand totals ignore table calc filters) by embedding the rank calc in the window sum.
However, there's a second potential issue and this is *the* key thing to keep in mind about working with customizing Grand Totals & Subtotals that are using the Automatic setting: *They are independent computations at their own level of detail*. In some cases Tableau will actually issue separate queries to the data source for GTs and subtotals, that helps me remember that these are separate computations.
In this view, the grain/level of detail of the detail rows is the Vendor dimension, the level of detail in the GT is the entire data set (after dimension filters and regular agg filters have been applied). Daniel Vincent's suggested calc from Re: LOD from multiple Sources? is able to evaluate for the GT/detail distinction because FIRST() and LAST() will return different values in the detail rows, while they will both return 0 in the GT row because there is only one "row in the partition/address/mark. I'd tend to use something like MIN([Vendor]) == MAX([Vendor]) for the evaluation because that avoids introducing unnecessary table calcs. (As much as I love table calculations I avoid them until I need them because of the complications they introduce into my worksheets).
The effect of this on the RANK calc used for cf_TopNonPO is that in the GT it's only returning a single value for that one mark, and that value is 1, so it's including everything.
There are two ways around this:
1 (SIMPLE)) If there are no other dimensions to be used in the view that would be grouping the vendors, I'd skip the table calcs and use a Top N filter. Then we don't need to mess around with custom GTs or table calcs at all.
I set that up in a few clicks on the Vendor field:
This is the 1) Top N Vendor worksheet in the attached workbook.
(If this seems like a "That's way too simple, I should have thought of that!" solution, the reason why it's 1st on the list for me is because there have been other forum questions where the original questioner had gone down a path of table calculation solutions and I came up with what they needed, and then someone else came along and said "You know that could be solved with a Set or a Top N filter" (or even just a regular aggregate calc). Which led to me having my own Homer Simpson "D'oh!" moments. The more general principle is to look at the data, the view, and the desired goal, and only take route the questioner has taken so far as a suggestion of what might be.
2 (COMPLICATED)) Unfortunately Top N filters only do one Top N, not a top N per partition. So if there is something used to group or partition the vendors and the goal is to have a Top N for each, we currently have to use table calcs. http://community.tableau.com/ideas/1316 is an idea to enable LOD expressions to do this.
The table calcs have a problem in this case, though: Because the grain/level of detail in the Grand Total is only one mark and RANK only returns a single value there isn't enough detail for the RANK calc to work. The solution for this is *not* covered in any of my grand total posts because there is no single generalizable solution given all the ways table calcs get used. The solution in this case is based on the increased level of detail solution in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/.
a) Start the view with Vendor on Rows and the AGG(cf_PO...) calc on the Filters Shelf, filtering for 1.
a) Duplicate the Vendor dimension and put that field on the Level of Detail Shelf.
b) Put the cf_TopNonPO calc onto Rows and set the addressing to an Advanced on both the Vendor and Vendor (copy) dimensions.
c) Create a calc for the amount of cf_CntOfNonPO with the formula:
IF FIRST()==0 THEN
WINDOW_SUM(IF [cf_TopNonPO] THEN SUM([cf_CntOfNonPO]) END)
d) Bring that into the view with a *nested* addressing on Vendor (copy), preserving the original addressing of the cf_TopNonPO calc.
The result is this workout worksheet:
e) Repeat steps c & d for every other measure you want to display.
f) Duplicate the worksheet and get rid of any unnecessary pills (like the original SUM() pill I'd left in the above for validation purposes).
g) Move the cf_TopNonPO calc to the Filters Shelf, filtering for True.
f) Go to Analysis->Stack Marks->Off to turn on the mark stacking in the GT. See http://drawingwithnumbers.artisart.org/customizing-grand-totals-in-tableau-v8-the-stacking-snag/ for details on why this is needed.
Here's the view:
The v9.1 workbook is attached, if you have any questions let me know!
Jonathan, thanks for the reply!
About the solution 1. It's so simple!
What I am not sure is:
The dimension Vendor filter and the top filter within, which is equivalent to a set filter,
is supposed to operate before the measure filter cf_POandNONPOVendor.
When p_Top=4, the top 4 rows will remain because of filtering on Vendor. When the measure filter cf_POandNONPOVendor is applied, less than 4 rows will stay.
But the result looks like the measure filter is applied before the dimension filter. What's going on here?
Or the top filter (set filter) is applied after the measure filter? I am confused.
On Solution 2:
What does the duplication of Vendor dimension and putting it in detail shelf do?
Yes I see that more addressing is needed for the ranking.
I'm confused as well, this is not behaving the way that Tableau has historically. I can replicate the issue in Superstore, I think there's a bug here. Will write up more when I get the chance.
Alexander, what version of Tableau are you running & what platform? I can replicate the issue (the agg filter looking as if it's applied before the Top N filter) on v9.1.0 on Mac, but can't on v9.1.1, v8.3.3, v9.0.4, nor v9.2 beta 3 (all for Mac). I just checked the v9.1.1 release notes and couldn't find anything there about it, however Tableau doesn't put all the changes in the release notes so it's of limited use.
9.1.1 on Windows 7 Enterprise to be exact.
My workbook is here
PS. Have they changed the order of operations?
PS2. Remember to have a look at my questions on Solution 2.
Thanks for these notes on what isn't necessary (I was moving a little fast).
When Tableau is computing a Grand Total on Columns the level of detail of the GT computation is effectively removing all the dimension(s) that are on the Rows Shelf. However, in order for the ranking to work we need a Vendor dimension, so we put Vendor (copy) on the Level of Detail Shelf where it will be included in the GT computation. Does that make sense?
I don't think the order of operations has changed, I think it's a bug because it doesn't replicate in all versions of Tableau that I have. I'm linking to a quick video I made yesterday demonstrating the problem. I'm sending this off to Tableau tech support.
Is it fixed in 9.2?
I see the problem in the original workbook in 9.2 beta 3, but in my replication using Superstore I didn't see the problem in 9.2 beta 3. So I don't know what's going on there...
Also, there is a workaround for this strange behavior: add the aggregate filter to the Vendor filter as a Condition so it's applied with the Top N filter:
Hi Jonathan and Alexander, I can't thank you enough for guiding me to how I want my reports to look. Only two weeks into playing around with Tableau and I am learning so much and it's due in part to the fantastic support in this community.
I like things simple so I went with your Solution 1, Jonathan. If I ever need to append additional dimensions in my view, I'll make sure to give Solution 2 a try.
Thanks again, your help is deeply appreciated. If I can buy you two a drink I would