2 Replies Latest reply on Jul 10, 2017 9:03 AM by Luke Sirakos

# Excluding a filter on a line graph?

I am using the super store data for this example, say I want to create a line graph that shows the sales over time for a sub-category and then compare it to the average sales for all different sub-category groups in that category.  I create one line graph that filters on sub-category Tables in the Furniture category.  I create another line graph just filtered on Furniture using a LOD calc to find the average across each subcategory. How can I put both of these lines on the same graph when the first has a restrictive filter? Is there a way to make the LOD calc ignore the filter?

Example workbook included.

• ###### 1. Re: Excluding a filter on a line graph?

hi Luke,

So this should do the trick for you...

If you want a value to not be affected by a regular filter, it will need to be a FIXED LoDs (these are calculated before any filters are applied). I've used the following calculation

[Avg Sales - Sub-Category in Category LoD]

{FIXED [Category], DATETRUNC('month',[Order Date]):

AVG( {FIXED [Sub-Category],DATETRUNC('month',[Order Date]): sum([Sales])})

}

So the inner part is doing the SUM by Sub Cat and Month...and then the outer part is taking the average of that value and applying it to the category. A bit of a brain teaser this one, but hopefully makes sense?

Let me know if not.

1 of 1 people found this helpful
• ###### 2. Re: Excluding a filter on a line graph?

Thanks Simon and great explanation! That really helped!