Custom Shape/Color Issue when showing multiple measures
I have a data set of cell phone data that looks like this:
Each cell phone at the company has its own row in the dataset, and the data shows a couple dozen metrics (only a few shown in my image).
I have been tasked with creating a dashboard that shows a number of these metrics such that we can easily identify the outliers and address them. I created a dashboard that does this, however when I add color and shapes, I get an unintended result.
You can easily spot the outliers, but because the data shows each cell on its own row, then every instance of that $800 LONG DISTANCE outlier has the same red alert icon...even in the metrics where that cell number is $0. In other words, that cell number has $800 in long distance charges that we want to identify, but we don't care that that cell number also had $0 in adjustments, other overages, or voice overages. But all of them have the same red alert icon.
Here's the calc I used to determine both the color and shape:
IF ([DATA_OVERAGE]>100 OR [LONG_DISTANCE]>500 OR [OTHER_CHARGES]>500 OR [SMS_OVERAGE]>200) THEN "RED"
ELSEIF ([LONG_DISTANCE]>100 OR [OTHER_CHARGES]>100 or [SMS_OVERAGE]>100) THEN "LIGHTRED" ELSE "GRAY" END
Aside from creating different worksheets for each metric and merging them together in a single dashboard (which I think would not only be cumbersome, but also may have some axis misalignment issues)...is there a better way to do this?
