3 Replies Latest reply on May 26, 2016 2:29 PM by Stephen Lavery

# Creating multiple reference lines by dimensions

See the image below.  I have 100 values plotted.  Each color represents a dimension.  Let's assume sales territory.  So, red is northeast, orange is central, etc.  Can you add multiple reference lines for each color represented.  That is, for all of the red values plotted, I would like to see the average of the red values.  For all of the orange values, I'd like to see the average of all of the orange values, and so on.

• ###### 1. Re: Creating multiple reference lines by dimensions

Hi Michael,

I was able to achieve this by using LoD calculations and a dual axis:

In my example I use the the superstore data set. First, I plotted the sales by category by week as normal and then I created an LoD calculation for the total sales for the year for each category:

{FIXED [Category], [Year]: SUM([Sales])}

I then divided this by the numeber of weeks in the year to get my average weekly sales by category:

SUM([Sales by Category by Year])/TOTAL(COUNTD([Week Number]))

Drop this out onto your view and make it a line. Then create your dual axis chart and synchronize the axes and hide the header for one of your axes. I then showed mark labels for the start of the average lines and that's it! See the attached workbook and let me know if this does the trick. I'm not sure if you use weeks in your example but it will be a similar logic.

Best,

Stephen

• ###### 2. Re: Creating multiple reference lines by dimensions

Thanks Stephen.  This worked perfectly.  I really appreciate the insight.

• ###### 3. Re: Creating multiple reference lines by dimensions

No problem! You're also able to mark my answer as correct so other future users will be able to find it. And I also get some forum points...