It doesn't. Make sense. Could you please post a packaged workbook with just Product ID, SKU ID and Category fields. (Nothing confidential there). Also set the workbook up the way you currently have it, and some indication of what you want it to look like. Thanks,
Sorry to hijack this thread... but I think I understand the basic problem. I have a similar issue when I want to list out student absence levels, or days suspended, etc. which must be aggregated. For example, I can write a calculation that puts absence levels into categories (like 1-5 absences, 6-10, 11-20, etc), but because its an aggregate, it is difficult to make lists of how many students represent the categories I've created. I can create bar charts based on distinct counts of student IDs, but the bar charts are broken into individual chunks based on Student ID on the level of detail and this creates problems with filter actions, etc.
It would be nice to be able to use these types of calculations "as dimensions" rather than discrete measures, so that filtering, actions, etc. would work as expected.
I may not be making any sense, either, but I've run into the problem many times and have yet to find another way to arrive at these values.
And did you post a package workbook with sample data? Noooooo....
Believe me, sample workbooks have been sent all over Tableau Support, been to the Tableau Doctor, etc. to no avail. I cannot post the workbook here, and it is a difficult issue to replicate in the sample data.
Same issue is discussed here, and there's a sample workbook I posted there that demonstrates this same issue: http://community.tableau.com/thread/131721
If someone can find another way to create the bars so that they aren't broken into chunks (without using static groups), I will buy them a beer and call them a genius.
Here's a better example of the problem I encounter. Notice the action filter only works properly if you select the header of the bar. I also cannot create a text table from the bar graph. I left the border on the bars on purpose; I realize I can remove them. But instructing users to click on the bar's header is not really an acceptable solution.
You can buy me a beer and call me a genius. See a couple of versions in the attached, which uses a table calculation and turns off Ignore in Table Calculations for the Class Size Categories measure.
However, this does not solve the problem with the Filter Action, because Filter Actions work on dimensions, not measures. Depending on whether/how it's implemented, this Idea might help: http://community.tableau.com/ideas/2399. In any case, I see this issue as an example of how when we start going out along one arm or branch of Tableau's functionality (like table calcs) and want to use that with to another branch (like Filter Actions) then we start having to use assorted workarounds.
If you really want this to work, I suggest pushing the class size calculation down into the data source so it is available as a dimension. Then you'd have a row-level calculated field returning a Class Size Category dimension, and then instead of a Filter Action on Class ID, the Filter Action would be on the Class Size Category.
Thanks Jonathan! This still has the problem that you cannot select the bar itself to filter to the details you want, as you mentioned--I'll buy you a beer, anyway, but I still don't have a proper solution for my problem. I have voted on your idea.
This example was someone else's, but will keep your suggestion in mind moving forward.
As always, I do appreciate your help!
Posting a HIGHLY SIMPLIFIED sample workbook here. What I am trying to do is get the measure "SKUs per product" to become a dimension so that I can calculate things like total sales against them (for example- I can understand average sales for products with 6 SKUs under them).
Let me know if this helps clarify my original question and please let me know if you have potential solutions!
Posted a sample workbook- see response below.
Can you just add the product ID, then hide those headers? See attached. Product is on the rows shelf, but I simply right clicked it and unchecked "Show Headers". If this isn't what you're after, I am not understanding what you want. Perhaps a mock up would help, in that case.
Thanks Matthew. This would work if the number of SKUs per product count were unique to every product. Suppose you have two products with the same number of SKUs under them (see attached for an example). I would like to know the average sales for a product with that many SKUs but if you simply hide the product heading, you will still show those two totals separately.
For instance, you have products 1 and 4 which have 6 SKUs under them. Ideally, I'd like to know the average sales for products 1 and 4 and see that on one bar (next to a SKU count of 6). However, this method would leave to separate bars on two separate SKU counts of 6.
Any takers on this? Would love some expert guidance!!
See the attached. The COUNTD(SKU) is set to discrete and "Ignore in Table Calculations" is turned off for the calc. that way we can partition the table calc Avg of Product Sales per SKU on the COUNTD(SKU). I set up a crosstab showing the workout of the view, along with a bar chart and a histogram view. For something like this the histogram view can be more accurate because the continuous COUNTD(SKU) on columns will fill in the missing gaps.
This is great and is really helpful. Thank you so much.
One other question for you- if I then wanted to bin the countd(SKU) dimension (for example 6-8 SKUs per product as one bar and 9+ SKUs as another), any ideas how I might do that?
You're welcome! To create custom bins, you'd create a calculated field like:
IF COUNTD([SKU]) <6 THEN
ELSEIF COUNTD([SKU]) >= 6 AND COUNTD([SKU]) <= 8 THEN
ELSEIF COUNTD([SKU]) >8 THEN
And use that in place of the COUNTD([SKU]) pill. Alternatively, you can do a dynamic binning using a calc like:
INT(COUNTD([SKU])/[your bin size])*[your bin size]