Forum Discussion

sobsawats's avatar
sobsawats
Frequent Visitor
9 years ago
Solved

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

 

 

  • sobsawats,

     

    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's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support

    sobsawats,

     

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