cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

Cumulatively SUM values for 2 years, specified by other columns

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Cumulatively SUM values for 2 years, specified by other columns

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

View solution in original post

8 REPLIES 8
Highlighted
Post Partisan
Post Partisan

Re: Cumulatively SUM values for 2 years, specified by other columnsn

Anyone can help me?

Highlighted
Post Partisan
Post Partisan

Re: Cumulatively SUM values for 2 years, specified by other columnsn

up

Highlighted
Solution Sage
Solution Sage

Re: Cumulatively SUM values for 2 years, specified by other columnsn

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.

lkalawski_1-1601013148981.png


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

 

Highlighted
Post Partisan
Post Partisan

Re: Cumulatively SUM values for 2 years, specified by other columnsn

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


Highlighted
Solution Sage
Solution Sage

Re: Cumulatively SUM values for 2 years, specified by other columnsn

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

 

lkalawski_0-1601016144198.png

 

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

Highlighted
Microsoft
Microsoft

Re: Cumulatively SUM values for 2 years, specified by other columns

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

9.25.4.1.png

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

 

Highlighted
Post Partisan
Post Partisan

Re: Cumulatively SUM values for 2 years, specified by other columns

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?

 

 

Highlighted
Solution Sage
Solution Sage

Re: Cumulatively SUM values for 2 years, specified by other columns

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (756)