5 Replies Latest reply on Jan 27, 2016 10:38 AM by Suk Lee

# Total Value that is not affected by filters

I am really struggling to define a total sales measure in the attached workbook that remains unaffected by the Profit filter.  I want to show the count of orders in profit per day and the total count of orders for that day to illustrate the comparative difference.  The attached file is just to simulate the problem, the real workbook is using a large extract so I want to avoid blending with a duplicate extract.

Cheers, Stu

• ###### 1. Re: Total Value that is not affected by filters

Hi Stuart-

You can create an LOD function for that.

LOD Quantity:

{fixed  : sum([Order Quantity])}

1 of 1 people found this helpful
• ###### 2. Re: Total Value that is not affected by filters

Stuart,

Dynamic parameter may be one of option to fix your issue.

Here is the steps to do it:

1. Create the profit parameter

2. Create calc. column that align with the above parameter

3. Bring the new calc. column to color mark

Just let me know your e-mail address if you need the workbook.

Best,

Suk

• ###### 3. Re: Total Value that is not affected by filters

It almost works... I changed the LOD slightly to {fixed[Order Date] : sum([Order Quantity])} when I get no values the "All Orders" is not preserved, ideally it would always be shown as in 1st and 9th of Jan:

Any ideas why this is happening?

Cheers, Stu

• ###### 4. Re: Total Value that is not affected by filters

Ok, I thought you wanted to fix the order date, but I was trying to replicate your all orders field without the filter.

When you are filtering on profit, you are eliminating the rows in that profit range.  The LOD is a row level value.

• ###### 5. Re: Total Value that is not affected by filters

Stuart-

Try this.  I ditched your profit filter, created a profit parameter and a new field that will drop a zero when the profit is less than the parameter value.  This works because it isn't filtering the row values out, it just replaces with a zero.

If you add a white border on the Order quantity marks, you won't even see the sliver of blue if you want it took look cleaner.

9.0.8 workbook attached.