Annual Income of customers. (CustId, AnnualIncome)
Monthly spending data for customers. (CustId, Month, Amount )
What I want to do :
1. Chart % of income spent by customers each month ( Amount / AnnualIncome ).
2. Create a 'band' and point out outliers. For example, if you spend more than $500 above or below the average, you are outside the band.Ideally this band would be bound by two lines (like Reference lines) and have its own background color.
Use line chart and 'Reference lines'.
1. The 'Reference lines' are just straight lines, and not the average of a particular month. ( i.e. they should follow the monthly average spend).
2. Don't know how to color the background bound by these lines.
Use combo chart
I can get the 'reference' lines follow the actual average (they are a measure).
1. I don't know how to apply multiple dimensions to Combo chart. I tried using drill-down but no luck.
Attaching example with both approaches.
My actual use case is more complicated than this (using aggr, rangesum, above ) and Reference lines don't seem to like that kind of expression.
A bit more on the 3 charts. The one on the right is static. Each customer has a measure that is calculated with Set Analysis. There could be a lot of setup which is OK but not if the customer list is not static.
The 2 on the left are dynamic and will support changing customer lists and work for long or short lists.
The one with the legend is autocolored by the engine 'by dimension'.
When you color by expression you can control the line colors . For example the following randomizes the color for customers but keeps black for the +/-500 lines. The only thing is that the legend disappears which i'm not sure why but would need to investigate. To enact the following change in the sample, go to either of the line charts, and under 'Colors and Legends' on the right, change the Color from 'auto' to custom and select 'expression' in the drop down. (notice you lose the ability to control/show a legend). then enter the following expression.
if(ChartDimension='500$ Below Average' or ChartDimension='500$ Above Average', black(), argb(255,rand()*255,rand()*255,rand()*255))
Which would be the dimensions? Month and Customer? Or just Month?
You can build the Combo chart as you described and visualize for each Month a bar and one or two points (lines). Also, you could use two Reference lines: one with the 500$ reference and another one with the total average for the whole year.