7 Replies Latest reply on Feb 21, 2016 5:36 PM by Shinichiro Murakami

# Adding Filtered Calculated Fields

Hi all,

Quick question regarding filtered fields.

I'm trying to add two columns of data.

Is it possible to filter one column and still be able to add the data together (only taking into account what is filtered.)

For example.

I am adding 100 from column X to column Y.

I filter column X, and 50 is left on column X.

My answer is still (100 + Y) but I want it to show (50 + Y)

I do apologise if this is not very clear, I'm in a bit of a rush.

Thanks!

• ###### 1. Re: Adding Filtered Calculated Fields

I'm sure it's possible but without more from you not much we can do with what you've given.

At the very least give a packaged workbook with your data, or similar dataset, mocked up with how you have the viz.  Then we can go from there.

• ###### 2. Re: Adding Filtered Calculated Fields

I have attached some sample data and a workbook.

E.g.

I would like to find Revenue Apples (UK Only) and Revenue Pears (ALL) by using filters. Is there any way of doing so.

Do let me know if you need more information or a better data source.

• ###### 3. Re: Adding Filtered Calculated Fields

Arjun

Put below formula and the field brings filtered Apple + Total Pears

[Revenue Apples UK + All Pears]

[Revenue Apples] + {SUM([Revenue Pears])} Thanks,

Shin

9.1 attached

1 of 1 people found this helpful
• ###### 4. Re: Adding Filtered Calculated Fields

Hi thanks for this!

This has worked on the test data, however when I use this on my original piece, the data seems to multiply?

For example using the [Revenue Apples] + {SUM([Revenue Pears])} brings about (280 x 2) + 100 = 660

(Revenue Pears x 2) + Revenue Apples UK.

Do you know why this could be the case? It seems to be counting the pears twice due to the { }?

Thanks.

EDIT: Tried on my original data, and the data is multiplying by the number of rows the filter contains e.g. if there were multiple United Kingdom rows...

I have tried to use - Count(Country) So that it returns the number of rows that are being used/filtered.

I then tried to insert this into the formula, [Revenue Apples] + {SUM([Revenue Pears])} / Count(Country) ...

However this generates the Agg/Non Aggregrated problem, is there anyway to get around this?

I also tried using a number instead of Count(Country) and the answer received was correct.

Attached a more updated test data to reflect the problem...

• ###### 5. Re: Adding Filtered Calculated Fields
1 of 1 people found this helpful
• ###### 6. Re: Adding Filtered Calculated Fields

Thank you! That is perfect!

Other question- would this work with averages?

• ###### 7. Re: Adding Filtered Calculated Fields

Calculating average is always tricky because of the level of aggregation.

But basically, sum / count at appropriate level should bring right answer.

shin