Sum rows only with specific values in other column

[pawelj795]

Hi,
I have database with inventory values like below. (sample data)


Now, I want to create measure, which gonna calculate YTD value, but only for Items, which never had Transtype 0 or 9.

[Anonymous]

Hi @pawelj795 . May be like this? SUM = CALCULATE(SUM(Value),Transtype <> 0,Transtype<>9)

[pawelj795]

@Anonymous 
That would be too easy 😉

I want to calculate YTD Value for items, which NEVER had TransType 0 or 9.
I mean by this, that some items could have all type of transactions (TransType) within a year.

[v-kelly-msft]

Hi @pawelj795 ,

First you need to create a calculated column as below:

year = YEAR('Table'[Date])

It is to get the year of your date,then,you need a measure as below:

Measure = 
CALCULATE(SUM('Table'[Visitors]),FILTER('Table','Table'[Trans_ type]<>0&&'Table'[Trans_ type]<>9))

Finally put your year column in the slicer, and measure in a card,and you will see :

You can choose any year you like to calculate the totalYtd value.

Here is my .pbix file,pls click here.

Best Regards,
Kelly

[pawelj795]

@v-kelly-msft 

Nope, it's not what I'm looking for.

Look on screenshot with my real data.

In my report, I need to exclude Items with no TransType 0 or 9.
I don't want to only exlude transactions, but whole Item.

In above example, Item with number 1-2-0105-0052 should be exclude entirely from my matrix.

[v-kelly-msft]

Hi @pawelj795 ，

First add a calculated column :

Column = 
var a=SEARCH(0,'Table'[ItemID],1,0)
var b=SEARCH(9,'Table'[ItemID],1,0)
Return
IF(a=0&&b=0,1,0)

Then correct measure as below:

Measure = 
CALCULATE(SUM('Table'[Visitors]),FILTER('Table','Table'[Trans_ type]<>0&&'Table'[Trans_ type]<>9&&'Table'[Column]=1))

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[pawelj795]

Hi @v-kelly-msft 

Shouldn't be TransType instead of ItemID in below measure and also OR instead of AND?

Column = 
var a=SEARCH(0,'Table'[ItemID],1,0)
var b=SEARCH(9,'Table'[ItemID],1,0)
Return
IF(a=0&&b=0,1,0)

Column =

VAR A = SEARCH(0;WH_Invent_Trans[TransType];1;0)

VAR B = SEARCH(9;WH_Invent_Trans[TransType];1;0)

RETURN

IF (A=0 || B=0;1;0)

[v-kelly-msft]

Hi @pawelj795 ,

a=0 means there's no 0 in the column, so is b ,so a=0&&b=0 means there's no 0 or 9 in column ID, you said all the rows which contains 0 or 9 in column ID and TransType should all be excluded...so in my measure I have excluded all the posibilities.I have checked my result,it satisfies your need,where do you find not work?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[pawelj795]

Hi @v-kelly-msft,
I tried to do what I want in your file and it still doesn't work.
Look on shared pbix.

https://drive.google.com/open?id=1DpCDU2v4xJwMyeXjbFMCJX5RAFQEZg6N



ItemID's which I marked should be also on the table rightward.
Why they aren't?

[pawelj795]

@v-kelly-msft 

Even with my above adjustments or with precisely your measure, it still only exlude transactions with trans type 0 or 9, not whole Index (ItemID)

[pawelj795]

@v-kelly-msft 
It's possible to do what I want?

[pawelj795]

up

[pawelj795]

up