Exclude with IF in SUM expression Set-Analysis

[Anonymous]

Hi All,

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

Category	Subcategory
A	1
	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

[sunny_talwar]

May be this

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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?

[sunny_talwar]

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])

[Anonymous]

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

To be continued:)

[Anonymous]

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!

[german24]

I hope it helps...

Regards.

German

[sunny_talwar · Accepted solution]

Seems to be working, isn't it?