2 Replies Latest reply on Mar 5, 2019 7:51 AM by Bryan Homer

# Need help with a calculation to exclude values based on selection

I cannot upload my data as it is sensitive information and my company would not appreciate that so i will try to explain my problem the best that i can.

I have 2 sheets that are both bar graphs and they contain 2 different measures

Sheet one - Measure 1

CountD

I

I    I

I    I    I    I   I

Product   A   B   C  D  E

Sheet two - Measure 2

CountD

I

I    I    I    I   I

Product   a   b   c  d  e

The problem is if i select "A" from sheet 1 the results on sheet 2 will include "a" which is the same product with a different name. For example if this Sheet 1 was video game consoles and "A" was playstation and if sheet 2 was additional gaming devices ps4 would be "a" , "b" would be pc, and so on. So ps4 shows up under playstation4. Sheet 2 has many more columns than sheet 1 so this happens for multiple columns in sheet 1. How can I exclude something from sheet 2 based on selection of sheet 1.

so if i select a column from sheet 1 can i create a calculation that basically says

IIF([measure 1]="playstation", 'Exclude (ps4)', 'Include(the rest')

IIF([measure 1]="nintendo 64", 'Exclude (n64)', 'Include(the rest')

etc...

sorry if i am unclear

• ###### 1. Re: Need help with a calculation to exclude values based on selection

Difficult to follow. Would you be able to create a mockup of the data and the workbook?

• ###### 2. Re: Need help with a calculation to exclude values based on selection

Sorry with the sensitive data I was having a hard time with a mockup but my coworker found a solution that involved making a calculation that when something was selected it would removed the unwanted item from the other sheet.

IF     [Category] = "LV"   and [Pivot1 Values1] = "V" THEN FALSE

ELSEIF [Category] = "FA" and [Pivot1 Values1] = "F" THEN FALSE

ELSEIF [Category] = "DE"      and [Pivot1 Values1] = "D" THEN FALSE

ELSE TRUE

END

1 of 1 people found this helpful