Given a table with the following schema:
| Date | Product | Units Sold |
|---|
| 1/1/2014 | Hat | 20 |
| 1/1/2014 | Sweater | 30 |
| 1/2/2014 | Hat | 10 |
| 1/2/2014 | Scarf | 5 |
I have a bar chart with the following requirements:
Display the Units Sold for the top 6 Products by Date for a given period.
Include an "Other" product to represent values not in the top 6.
Sort the bar segments according to the rank of Units Sold on the last Date of the given period such that the Product with the most sales over the period is assigned to the first (bottom) bar segment, the next highest product gets the next highest bar segment, etc.
So, I create the chart with two dimensions (Date and Product) and an expression of sum([Units Sold]).
I set a Dimensional Limit of Top 6 on Product, and enable "Other".
In the Sort expression I copy the chart expression and add set analysis resulting in sum({<Date={$(=max(Date))}>}[Units Sold])
This works exactly as I want with the exception that the chart segment assigned to "Other" isn't affected by the Sort expression and always ends up being stuck on the top of each bar.
So, is there a way to modify my Sort expression so that it affects the "Other" segment or do I have to disable the built-in "Other" value and calculate an "Other" value myself and then include my "Other" calculation in my Sort expression? If the solution is to calculate "Other" myself, I'd appreciate any pointers on how to do that given that I've still got the Top 6 dimensional limit in place.
Thanks.
Steven
