In terms of how to graph this, to start it looks like a crosstab that you could then get fancy with by turning into a heatmap. I'd suggest that you set up the Product Penetration Delta as a dimension in the view and put that on the Columns shelf, with Sales Rep on the Rows shelf. Then your Customer Count field would go on the Text shelf, so you'd end up showing one cell per Product Penetration Delta/Sales Rep combination. Once you get that working, then you could change it into a heatmap where squares are sized and/or colored based on the Customer Count.
There are some other options you might try, but the # of Deltas is limiting because 24 different categories per sales rep gets to be too many for people to be able to make useful distinctions when using color, lines, etc. You might try creating some custom groups of the Deltas to shrink that down, and then work with other types of views.
Hi Bruce, I'm not sure what your data looks like, so I can't be sure of what the calculations are doing. Can you post a a packaged workbook with some sample data?
On my first pass I could see that the Delta Product Mix calculation is definitely only returning one value per sales rep. Besides the Product Penetration view, you can see this if you drag the Delta Product Mix measure onto the Rows shelf in the Product Mix Detail view - there should be multiple rows per sales rep. This is because you've taken customer off of the level of detail, so the computation is happening over all customers and not for each customer.
So, here's what you need to do in the Product Penetration view:
1. Drag custNo onto the Level of Detail shelf. You will now see the spread out Product Mix, with a lot of fuzzy looking 1's because the noCusts calculation that is COUNTD([custNo]) is now working at the level of detail of custNo so it only returns 1 for each customer.
2. Create a table calculation - I called it "Customer Count" using the formula:
IF FIRST()==0 THEN
3. Drag that field onto the Text shelf. You're still going to see lots of 1s.
4. On the Rows shelf, click on the blue AGG(Delta Product Mix) pill and uncheck "Ignore in Table Calculations". This will let the Delta Product Mix field be included as a dimension in the table calculation. (Normally Tableau ignores calculated dimensions).
5. Click on the green Customer Count pill on the Text shelf and set it to Compute using custNo. You will now see a correct batch of numbers.
ProductPenetration jtd edit.twbx.zip 501.0 KB
1st, I want to say wow for taking the time to understand my problem
2nd, I want to say wow for explaining the solution in great depth, It has helped my understanding of the product and those silly calculations.
Question: When I right click and select view data, I only see 1 customer no matter what the count is. Can I change this?
Again, thanks a LOT! You Rock!
The Customer Count is a calculated field that is shown at the intersection of Delta Product Mix and sales rep, and regardless of whether there is overlapping text you're only seeing the "topmost" row of data. The IF FIRST()==0 ... END bit in the Customer Count calculated field is there to prevent Tableau from showing overlapping text in the view, even if you comment that part out then View Data only shows one customer.
I tried creating a dashboard with the Product Penetration view and a separate view that would have an Action Filter to show customers for the given intersection of rep and Delta Product Mix, but it seems like Tableau won't consider Delta Product Mix to be an allowed dimension for the target in the Action Filter, (probably because it's a calculated dimension based on a table calc, you can create Sets on other calculated fields). I also tried to create a Set on the sales rep and Delta Product Mix, or even just Delta Product Mix, but Tableau won't allow that either, I'm guessing for the same reason.
You could create a different crosstab to show customers by just duplicating the view and dragging custNo onto the Rows or Columns shelf, and do some separate filtering and sorting by Delta Product Mix and sales rep, but that's about all I can think of.