Hi,
I have created an stacked bar chart Profit (bin) per Sales by category. I would like to highlight the highest sales bar with a different colors.
I have created this formula IF SUM([Sales])=WINDOW_MAX(SUM([Sales]))THEN 'MAX'
ELSE 'OTHER'
END
But it does not work as it has to include the categories.
Is there any way to do it?
Thanks,
Esther
You're almost there but you can use an EXCLUDE LoD to ignore the Category. You should also ignore all of the lower levels of the hierarchy on Category as well, but I woujldn't recommend having a hierarchy on the colour button. This calculation works for Category, but because expanding the hierarchy only adds things to the Detail, rather than the Colour button, I think that causes problems with the colouring.
IF
SUM({EXCLUDE [Category],[Sub-Category],[Manufacturer],[Product Name]: sum([Sales])}) =
window_max(SUM({EXCLUDE [Category],[Sub-Category],[Manufacturer],[Product Name]: sum([Sales])}))
THEN "Max" ELSE "Other" END
