Forum Discussion
sobsawats
9 years agoFrequent Visitor
SUM only same product UoM
Hi all,
I have transaction of product and its multi level product group, each product line has quantity which I want to sum it to its uppler product group. The condition of sum is if product has same UoM (unit of measurement like KG, TON, BOX), sum it to product group but if not same UoM, give zero to product group.
The picture of sample data and expected result below may help to describe my problem
I am trying to use SUM, EARLIER to solve but seems no luck
sum2 = SUMX(FILTER(txn,(txn[UNIT]=EARLIER(txn[UNIT]))&&txn[L7]=EARLIER(txn[L7])),txn[QTY])
Please kindly help to enlight me. Thank you
You may add measures as follows.
QTY2 = IF ( DISTINCTCOUNT ( txn[UNIT] ) = 1, SUM ( txn[QTY] ), 0 )
UNIT2 = IF ( DISTINCTCOUNT ( txn[UNIT] ) = 1, MAX ( txn[UNIT] ), "NA" )
2 Replies
- v-chuncz-msft
Community Support
You may add measures as follows.
QTY2 = IF ( DISTINCTCOUNT ( txn[UNIT] ) = 1, SUM ( txn[QTY] ), 0 )
UNIT2 = IF ( DISTINCTCOUNT ( txn[UNIT] ) = 1, MAX ( txn[UNIT] ), "NA" )
- sobsawatsFrequent Visitor