Forum Discussion

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost 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
Need Help
Show and Tell

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • pawelj795's avatar
      pawelj795
      Icon for Post Prodigy rankPost 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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity 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