You're going to need to create a combined field of all the dimension(s) you want to do the Top N for and then build your set off that. So if you want the Top N to be based on product, country, and year, then that's what you'll use.
Thanks for the lead. I made the changes (see Sheet 2) and it is getting closer to what I am expecting. The outstanding portions are:
- how do aggregate the individual % of the Top N, I used the default subtotal and while the total "Sales" is what I am expecting the "Percent of Total" is not as expected. As an example (see attached picture), the red circle should be 38.19%+29.43%+20.01%=87.24%.
- in combining the fields "Country/Region", "YR(Order Date)" and "Item", I utilized the year of the order date. How could I carry something similar but need to do it in fiscal year (starting from July)? I duplicated a Order Date and set the Fiscal to start from July - Order Date (FY). If I try to create a calculated field (datename('year',[Order Date (FY)]), the result seems to be same as (datename('year',[Order Date]).
Thanks in advice for any advice.
The fiscal date that you set it only for display, not for calculated fields (unfortunately). So you're going to need to build your own field to set the year, such as: IF MONTH([Date]) >= 7 THEN YEAR([Date])+1 ELSE YEAR([Date]) END.
thanks for the code for the Fiscal year, it worked. BTW, any recommendation on how to fix issue (1), to aggregate the percentages correctly?
This is complicated and I'm not 100% sure can be done. Tableau's grand totals are computed as a separate computation of the given measure (% of total in this case) at a different level of aggregation, *not* an aggregation of what is displayed in the view, which is what you're asking for. This will return the same results in many cases, but not all. There's an Idea to have Tableau do what you want, btw: http://community.tableau.com/ideas/1232.
I did a series of posts on how to customize totals, starting with http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/. However, what you're wanting goes beyond that in a couple of ways:
- The goal is to aggregate the % of total table calc in a different way in the subtotal row. This requires a technique that I've only done a few times (and haven't seen anyone else do it) and have yet to write up, I'm still learning the ins and outs of it and have yet to apply it to subtotals, which add a level of complexity.
- The % of total is filtered by a table calc. What's in the grand total and subtotal rows are a separate computation, that is performed in parallel with table calc filters. In other words, grand total and subtotal rows don't aren't affected by table calc filters. That means that we have to make all the table calcs work inside the subtotal rows and effectively implement the filters there. To do that (which I imagine can be done, but I'm not sure) requires increasing the level of detail in the view to have the necessarily granularity within the computation that happens within the subtotal row, and given that we're using a % of total table calc means that that Compute Using (which is already a bit complicated) will need very particular attention so that it continues to work in the regular rows.
Customizing totals of table calculations is like trying to do watch repair in the dark. We can't see the ins and outs of what Tableau is generating for the totals the way we can in a regular crosstab, we have to imagine it and that makes it quite a bit more difficult. Right now I'd say customizing subtotals of table calculations with advanced compute using is like trying to do watch repair in the dark while wearing rubber gloves. I can look at this, but I can't promise success and given my schedule would take a few days (at least).