3 Replies Latest reply on Jun 3, 2019 5:56 AM by Ken Flerlage

How to get color ranges WITHIN categories

I use average price on the color pill. I have # of sales BY CATEOGORY for each month. Right now the color pill creates a range for ALL products across months. How can I make the color ranges specific to EACH PRODUCT vs. ALL?

• 1. Re: How to get color ranges WITHIN categories

Unfortunately, you can't use separate scales when you're using the same measure. However, you could create a calculated field that sort of normalizes the value. In your case, I'd create a calculated field that gets the maximum price for a given product/month, then calculate the percentage of that for all the other months. Then color by that. Since you didn't attach a workbook (please consider doing so in the future as there are almost always some other gotchas hidden in the actual workbook), I've created the following with SuperStore:

This has a similar problem to yours. Art, for instance, shows very light colors because Chairs have such higher sales. But I created the following calculated field:

Product % of Max

// For each sub-category, get the % of the max.

// Use this to color each sub-category on its own scale.

SUM([Sales])/ATTR({FIXED [Sub-Category]: MAX({FIXED [Sub-Category], MONTH([Order Date]): SUM([Sales])})})

Then I color by this and I get a different color scale for each sub-category. The largest is always dark blue and the rest adjust from there.

See attached.

1 of 1 people found this helpful
• 2. Re: How to get color ranges WITHIN categories

Thanks Ken!

SUM([Sales])/ATTR({FIXED [Sub-Category]: MAX({FIXED [Sub-Category], MONTH([Order Date]): SUM([Sales])})})

Can you help me interpret this?

Sum(Sales) = All sales correct? Don't you need to fix this at subcategory level?

Why are you using ATTR?

So you are taking ALL the sales for a month and then seeing what percentage that category is of total sales? And then coloring based on that percentage?

I think the issue is that in my case I'm dealing with sales averages. What is the average price charged for a unit. Is it conceptually the same? It might be I think....

• 3. Re: How to get color ranges WITHIN categories

Yes, that's correct. We're getting the percentage for each month/sub-category as a percentage of the highest monthly total in that sub-category. The result is that the month with the highest sales will be 100% and the rest will be some percentage of that.

SUM(Sales) is not fixed because it operates at the level of detail we're actually working with in the moment (month and sub-category). The rest is fixed because it needs to step outside of the current level of detail to find the max for a given month.

Because SUM(Sales) is an aggregate, the second part of the calculated field must be an aggregate. I used ATTR but you could also use MIN, MAX, AVG, as they'll all return the same value.

With averages, the calculation might be slightly different, but the concept is the same. I'd be happy to show you how with some real data if you could share it.

1 of 1 people found this helpful