6 Replies Latest reply on Feb 11, 2019 2:40 PM by Mihai Constantinescu

# Removing High Variance Items In Aggregated View

Hi,

I am attempting to remove items with a high % variance based on a parameter. For example, Price A is \$1 and Price B is \$4. 4/1 = 400%. I can create a calculation that says "% variance > X%", and it works fine in a view that lists out the products.

However, if I remove the products and want an overall view summed up to a category, the calculation doesn't work.

Works at the product level.

When products aren't in view, the % variance doesn't work, and still show all variances.

• ###### 1. Re: Removing High Variance Items In Aggregated View

Can you attach a sample workbook or more details about your calculation?

• ###### 2. Re: Removing High Variance Items In Aggregated View

Thanks, see attached.

• ###### 3. Re: Removing High Variance Items In Aggregated View

not sure what you meant, e.g. for Home Office 64% is correct for those Sales/Quantity

• ###### 4. Re: Removing High Variance Items In Aggregated View

would recommend formatting the Variance to number custom with % as Suffix

and then change your param to this display format

• ###### 5. Re: Removing High Variance Items In Aggregated View

Thanks, Mihai.

Attached is the same workbook, but I removed the "segment" from the top graph to better make the point.

Notice how I have this dashboard set up to show only items with greater than 4,000%. When I include customer name, you can see the grand totals. When I remove customers and aggregate it up, the totals change.

I need the totals to match exactly whether aggregated or not.

That make more sense?

• ###### 6. Re: Removing High Variance Items In Aggregated View

There are a few things to mention:

1. that is not 7735% but actually 77.35% = 1952270/25238

2. one sheet has a ABS( [% Variance]) < [X % Variance Removal] filter the total one doesn't ... thus, you are comparing different things (remove the T/F filter to see the difference)

3. if u use {FIXED  [Customer Name] : ABS( [% Variance]) < [X % Variance Removal]} in the other one you will get 77.35%