calculating sum or max of duplicated rows in 1 col w/ duplicated rows/or not duplicated in another

[PMY1965]

Please help

Please help me.  Thanks a lot.  Power BI DAX. I need to solve this :

1.  duplicated TransNo rows with duplicated TotalDiscAmt2 will get MAX value;

2.  duplicated TransNo rows with different TotalDiscAmt2 will get SUM value;

3.  Row with Unique TransNo gets TotalDiscAmt2 value. How to do it?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create sample pbix file like below.

Please check the below picture and the attached pbix file.

It is one of ways for creating a measure.

expected result measure: =
SUMX (
    VALUES ( data[trans_no] ),
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( SUMMARIZE ( data, data[total_disc_amt] ) ) ) = 1
            && CALCULATE ( COUNTROWS ( data ) ) = 1, CALCULATE ( SELECTEDVALUE ( data[total_disc_amt] ) ),
        CALCULATE ( COUNTROWS ( SUMMARIZE ( data, data[total_disc_amt] ) ) ) = 1, CALCULATE ( MAX ( data[total_disc_amt] ) ),
        CALCULATE ( SUM ( data[total_disc_amt] ) )
    )
)

[PMY1965]

Just Say Wow. It does work amazingly. It took me one week crazily until this morning.  

Thanks a lot.

xxx

[PMY1965]

Hi there, 

I wish to share the right results I get with Measure

DiscountCalculationMeasure =

VAR CurrentTransNo = SELECTEDVALUE ( 'AR_SalesTrans'[TransNo] )

VAR DuplicatedTransNo =

    CALCULATE (

        COUNTROWS ( 'AR_SalesTrans' ),

        ALLEXCEPT ( 'AR_SalesTrans', 'AR_SalesTrans'[TransNo] )

    )

VAR IsUniqueTransNo = IF ( DuplicatedTransNo = 1, 1, 0 )

VAR MaxDiscAmt =

    CALCULATE (

        MAX ( 'AR_SalesTrans'[TotalDiscAmt2] ),

        'AR_SalesTrans'[TransNo] = CurrentTransNo

    )

VAR SumDiscAmt =

    CALCULATE (

        SUM ( 'AR_SalesTrans'[TotalDiscAmt2] ),

        'AR_SalesTrans'[TransNo] = CurrentTransNo

    )

RETURN

    IF (

        IsUniqueTransNo = 1,

        SELECTEDVALUE ( 'AR_SalesTrans'[TotalDiscAmt2] ),

        IF ( MaxDiscAmt = SELECTEDVALUE ( 'AR_SalesTrans'[TotalDiscAmt2] ), MaxDiscAmt, SumDiscAmt )

    )