It appears that the key to making the cell colored revolves around the MIN(ONE) pill. When I remove it and then place it back I can't get the formatting to work. I did make the ONE measure Minimum. What other formatting needs to occur to make this work. I am attaching what I end up with after I recreate the pill. My version is ugly
One issue is that your sales color calculation needs to be aggregated in this example--because you may have several discrete sales figures for each product container in each region/customer segment combination. So, wrap each [Sales] field in SUM() inside the calculated field for your colors.
However, there seem to be other weird issues with being able to apply this formatting in your workbook. I'm looking at it right now, trying to figure out what is happening.
1 of 1 people found this helpful
Here's a version 8.1 workbook with a possible solution (the overlapping colors can be adjusted with the SIZE() control, but its difficult to make them perfect).
Figured out your other problem. The Axis range was set to be 0-3, if you make the maximum value of the axis 1, and aggregate your coloring calculation, then you'll get the results you want. See attached.
Awesome! Thank Matt. I think the editing of the axis is the key I was missing in recreating what Josh had done earlier.
Appreciate the help on that one.
One quick way to fix the colors and prevent them from overlapping is the use of a blank field. By creating a calculated field set to " " and using this on the Columsn and Rows shelf, we can create a partition for dividing the view.
I have updated the provided workbook example and attached it here with a field named [blank] added to both the rows and columns.
The gray lines can be removed via formatting if desired and this is accomplished on sheet 4 of the workbook.
2 of 2 people found this helpful
It had been a while since I commented on this one. Since then, I learned that you can also easily accomplish separated colors by using the "Bar" mark type. See attached screenshot. This does not create the partitions as in the "blank" method, although you can use borders to further separate them out.
It's nice that we have multiple ways how to solve problems
Yes sir... I wish we had a repository of "related solutions" or some way to organize threads with correct answers for specific topics. Not sure what the best solution would be, but there's certainly a ton of solutions, workarounds, and examples that get buried within this Forum.
I struggled with these overlapping colors in crosstabs like this example in the past, and then noticed a workbook Joshua Milligan created at some point, , that had the mark type set to "Bar" - I had never seen this mentioned or documented before then. This forum is invaluable, in terms of picking up additional tips and tricks, best practices, etc. I just sometimes find it difficult to organize what I learn/find here.
Matthew - by the way - great solution with the bars. I've never done that before.
Regarding the suggestion to more easily organize threads, I'm curious if adding appropriate tags to the workbook repository might accomplish this (to some degree)? I know locally, I keep folders named 'formatting', 'table calculations', 'statistics', 'data blending', etc.. but often the solutions overlap categories. Tagging at least limits the search to some degree.
That's a clever little trick Joshua. Thanks for sharing!