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

 

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost 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.

  • pawelj795's avatar
    pawelj795
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    up

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      pawelj795
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      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)
      • pawelj795's avatar
        pawelj795
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        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)