Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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

1 Solution

Accepted Solutions
MVP

Seems to be working, isn't it?

12 Replies
MVP

May be this

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

Anonymous
Not applicable
Author

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

MVP

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

Anonymous
Not applicable
Author

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?

MVP

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
Not applicable
Author

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
Not applicable
Author

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!

Partner - Contributor III

I hope it helps...

Regards.

German

MVP

Seems to be working, isn't it?

Tags
Community Browser