Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PMY1965
Advocate I
Advocate I

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

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?
 
PMY1965_0-1715140968745.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1715143015826.png

 

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

  


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1715143015826.png

 

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

  


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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

Thanks a lot.

xxx

PMY1965
Advocate I
Advocate I

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.