I am attempting to vary colors on a pivot chart based on values to use as an alternative to KPIs in PowerPivot. In the attachment is a simple example. If the value is -1 in the pivot, then the chart will be a 1 and red in color. If the value is 0, then the chart will be a 2 and blue in color. If the value is 1, then the chart will be a 3 and green in color. I'm fairly sure you can't vary colors based on values in the format options pane for a single chart. I did manage to create three different charts that only show a color for each value. I.e., one chart works when the value is only -1 and will show a red circle but will remain blank for any other value; the same idea is applied for the other two charts. Is there a way to overlay these charts (e.g., transparency, grouping, etc.) to effectively create one chart assuming the limitations of formatting a single chart? If I can achieve my desired result by just one chart then that is of course fine as well.
Note: the Shipper slicer will give different values in the pivot and thereby the charts. Atlanta = 1 (Fail), Detroit = 2 (OK), Nashville = 3 (Pass).
Bookmarks