4 Replies Latest reply on Jun 21, 2018 12:02 AM by David Atwell

# IF Statement based on a filter

I am looking to create a calculated fields based on the selection of a Filter.

Based on the table below, when I choose the Payment Type (Tax), I get the amount \$548M.

What I want is that when I select Payment Type (GST), I only want to see the Total GST amount of \$980M. (Note: Total GST  = GST 3 - GST 2 - GST 1). At the moment, it sums all the GST Payments types together.

I know i am able to Filter out on the data source page for GST 1, 2 and 3 but I need it for other visuals.

Can this be done? Maybe I need to do it via Parameter but I a really don't want to as I have several years for the data. (not shown in example). I have provided an example of this in a workbook.

Any help is appreciated, David

• ###### 1. Re: IF Statement based on a filter

Hi David,

I can think of two solutions; one is creating a calculated field returning only the Total GST Value, something like:

IF [GST Payment Type] = 'Total GST' then [Amount] end

Another option is creating similar lookup filter to only filter the view. This is a good explanation of this technique.

I attach sample workbook with the first solution.

• ###### 2. Re: IF Statement based on a filter

HI David,

Not sure about your requesting view's image, but hide these values in particular sheet?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: IF Statement based on a filter

Thank Michael.

Using your methodology I have tried something else. The user will only be able to select the Payment type filter, therefore I have created something like:

IF [Payment Type] = 'GST/BAS' AND  [GST Payment Type]= "Total GST"  then [Amount]

ELSE [Amount]

END

The calculation is valid but not quite providing me on the the Total GST amount (still includes GST 1, 2 etc.)

• ###### 4. Re: IF Statement based on a filter

Thanks for the suggestion Shinichiro.

Although it doesn't solve how it interacts with my dashboard, your suggestion has triggered some thought. I have been able to use Exclude in my filters and get the answer I want. Easy as that.