19 Replies Latest reply: Apr 9, 2015 10:15 AM by Ade Jgo RSS

    Adding Avg line in a line chart

    Ade Jgo

      What I have :

          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.


       

      My attempts:

      Use line chart and 'Reference lines'.

          Problems:

                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).

          Problem:

                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.