Forum Discussion

Icon for Post Prodigy rank

Post Prodigy

6 years ago

Sum rows only with specific values in other column

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.

data protection

Anonymous

Not applicable

6 years ago

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

pawelj795
Post Prodigy
6 years ago
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
  Community Support
  6 years ago
  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
    Post Prodigy
    6 years ago
    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.