Cumulatively SUM values for 2 years, specified by other columns

[pawelj795]

Hi, 

I want to cumulatively SUM Item ID values (for 2019&2020), only for Items that never had Transaction Type = 0 or 9.

To clarify, if a particular Item has even one row with transaction type 0 or 9, then every row for this item should be excluded.

Furthermore, every row which have BLANK in column QTY should be excluded too.

These two above conditions aren't dependent.
I mean if the particular item doesn't have rows with transaction type 0 or 9, however, it has some rows with BLANK in column QTY, only rows with BLANK should be excluded, not every row for this item.

I realize that it could seem to don't make much sense, but I tried to really simplify my case, as much as possible.

Below sample file.

https://drive.google.com/file/d/1sl4SrIB4DGfe8xUh-K50FCMEn1i4Rh3_/view?usp=sharing

My problem is, how to write a DAX measure for this case?

Thanks in advance.

[v-eqin-msft]

Hi @pawelj795 ,

According to my understand, you want to calculate the sum of Values when the Transaction Type is not 0 & 9 and the QTY is not blank, right?

You could use the following formula:

Measure =
CALCULATE (
    SUM ( Sheet1[Value] ),
    FILTER (
        'Sheet1',
        'Sheet1'[Transaction Type] <> 0
            && 'Sheet1'[Transaction Type] <> 9
    )
)
    - CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER (
            'Sheet1',
            'Sheet1'[Transaction Type] <> 0
                && 'Sheet1'[Transaction Type] <> 9
                && 'Sheet1'[QTY] = BLANK ()
        )
    )

Here is my pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[pawelj795]

Thanks @lkalawski.
We almost there, your measure correctly choosing Items, but sums are incorrect, could you verify?

UPDATE.
I know what's wrong, your measure don't include rows with QTY < 0 -> maybe it's my fault because I didn't add that kind of rows to my sample. (by the way, when qty <0 it is adjustment)

How to modify your measures?

[lkalawski · Accepted solution]

Hi @pawelj795 ,

One change and it should work:

TransactionType09 = 
VAR _transactiontype09 = CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[ItemID] = MAX(Sheet1[ItemID]) && Sheet1[Transaction Type] IN {0,9}))

RETURN
SWITCH(TRUE(),
    _transactiontype09 > 0, BLANK(),
    SWITCH(TRUE(),
        MAX(Sheet1[QTY]) <> BLANK(), 1,
        0)
)

_______________
If I helped, please accept the solution and give kudos! 😀

[pawelj795]

Anyone can help me?

[pawelj795]

up

[lkalawski]

Hi @pawelj795

You can do the first part with these two measures:

TransactionType09 = 
VAR _transactiontype09 = CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[ItemID] = MAX(Sheet1[ItemID]) && Sheet1[Transaction Type] IN {0,9}))

RETURN
SWITCH(TRUE(),
_transactiontype09 > 0, BLANK(),
1)


Sum of Value = CALCULATE(Sum(Sheet1[Value]), FILTER(Sheet1,[TransactionType09] = 1))

However, I don't understand the second part.

If item has not 0 or 9 in the transaction type field (as shown in the figure above), we add only the values with QTY filled? For that one case, it would be Sum (Value) = 10?  Do we exclude items that have 0 or 9 in total sum, or do we sum them up?
Could you give an example?

_______________
If I helped, please accept the solution and give kudos! 😀

[pawelj795]

@lkalawski 

Thanks for your response.

Exactly, in your case the sum should be 10, 

Do we exclude items that have 0 or 9 in total sum, or do we sum them up?
Yes, we exclude every item, that have even one transaction with 0 or 9.

[lkalawski]

@pawelj795

Please use this measure and Sum from the previous post:

TransactionType09 = 
VAR _transactiontype09 = CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1),Sheet1[ItemID] = MAX(Sheet1[ItemID]) && Sheet1[Transaction Type] IN {0,9}))

RETURN
SWITCH(TRUE(),
    _transactiontype09 > 0, BLANK(),
    SWITCH(TRUE(),
        MAX(Sheet1[QTY]) > 0, 1,
        0)
)

I noticed there are duplicates in the table you uploaded, so keep that in mind when checking the totals.

_______________
If I helped, please accept the solution and give kudos! 😀