12 Replies Latest reply: Feb 8, 2017 4:05 AM by Mike Slottje

# Exclude with IF in SUM expression Set-Analysis

Hi All,

let's assume I have the following categories and the measure Nett Amount:

Category

Subcategory

A1
2
3
B
C
D

As measure, I want to use the SUM of sales of all the categories: SUM([Nett Amount])

To get an accurate overview of the sales, I want the SUM Nett Amount of all the categories without subcategory A2.

So there is one field that has to be excluded.

I tried such an expression like: SUM((IF(Not match(Subcategory,2)Subcategory))[Nett Amount]), but that didn't work.

How do I write such an expression?

Thanks a lot!

Mike

• ###### Re: Exclude with IF in SUM expression Set-Analysis

May be this

Sum({<Subcategory -= {2}>} [Nett Amount])

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Hmmmm that didn't work. The subcategory is still in there..

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Would you be able to share a sample to show the issue?

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Hmmm that's a bit difficult because I have to share our sales data.

For our analysis, we want to exclude one certain item number which is messing up our margin.

Of course I can do this with a filter pane, but I'm trying to find a solution where I can calculate our sales per i.e. customer withouth this specific item number.

Should I create some example data?

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Example data should be helpful, but just to make sure, were you sure to put a small negative sign in front of equal?

Sum({<Subcategory -= {2}>} [Nett Amount])

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Yeah I have the negative sign in front of the equal sign. I will post some example data later on today!

To be continued:)

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Attached a simple sample file.

The file shows customers, item numbers, nett amount (revenue) and margin %.

When you have a look at the data, you'll see that item number C has been sold only one time with a high revenue and a high margin.

This item number C is an item which i.e. does not belong to the normal assortment of the company and should therefore be excluded from the analysis.

Hopefully this helps!

• ###### Re: Exclude with IF in SUM expression Set-Analysis

I hope it helps...

Regards.

German

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Seems to be working, isn't it?

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Yeah thanks a lot Sunny! When having a look at your testfile, I realised I had to add the expression to both of my measures in my scatter plot.. That was pretty dumb, bt it works exactly as I wanted now:)

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Another example of the solution including a bookmark option if you want to keep C in your charts and just exclude in particular circumstances.

• ###### Re: Exclude with IF in SUM expression Set-Analysis

Thanks for your reply Andy! I was familiar with the bookmark, but that is indeed a good solution if you want to be able to switch between the two options:)