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

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

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

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

    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!