Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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! 😀
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 ()
)
)
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
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?
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! 😀
Anyone can help me?
up
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! 😀
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.
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! 😀
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |