Hello,
I have a bar graph that displays a per capita cost broken down by location on multiple levels (i.e. state, city). I've also put in a reference line that dynamically calculates the average per capita cost depending on how drilled down the location is. I'd like to color the marks (bars) in a bimodal fashion: if the per capita cost for that row is greater than the average, be dark; otherwise, be light. However, I only see how to give a hard-coded center value for the 2-step color. Is there a way to make a dynamic center value of AVG(PER CAPITA COST)?
Thanks,
Jane
I think you can utilize the WINDOW_AVG function to do this.
1) Create a calculated field as an IF statement (let's call it "Bar Color":
IF SUM([PerCapitaCost] >= WINDOW_AVG(SUM([PerCapitaCost]))
THEN 'Above'
ELSE 'Below'
END
2) Add the "Bar Color" field to the colors shelf for PerCapitaCost.
3) It may require you to specify that you want the WINDOW_AVG table calculation to calculate "across" your table, or "Compute Using" your date field...
Does that help?
