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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Rsanjuan
Helper IV
Helper IV

CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN

I have a table with three columns, Job Number, Grand Total, and Account Name:

 

Capture.JPG

 

 What would be the Dax expression to only calculate the sum grandtotal for a unique job?  For example, KOL16800004, should only be $17600.  Thanks!

 

 

 

1 ACCEPTED SOLUTION
v-haibl-msft
Microsoft Employee
Microsoft Employee

@Rsanjuan

 

In this scenario, you can first create a column to identify the duplicate rows.

DuplicateRows =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        EARLIER ( Table1[Job Number] ) = Table1[Job Number]
            && EARLIER ( Table1[Grand Total] ) = Table1[Grand Total]
            && EARLIER ( Table1[Account Name] ) = Table1[Account Name]
    )
)

CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN_1.jpg

 

Then create a measure to get the grand total.

Total = 
IF (
    MAX ( Table1[DuplicateRows] ) > 1,
    MAX ( Table1[Grand Total] ),
    SUM ( Table1[Grand Total] )
)

CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN_2.jpg

 

Best Regards,

Herbert

View solution in original post

2 REPLIES 2
v-haibl-msft
Microsoft Employee
Microsoft Employee

@Rsanjuan

 

In this scenario, you can first create a column to identify the duplicate rows.

DuplicateRows =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        EARLIER ( Table1[Job Number] ) = Table1[Job Number]
            && EARLIER ( Table1[Grand Total] ) = Table1[Grand Total]
            && EARLIER ( Table1[Account Name] ) = Table1[Account Name]
    )
)

CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN_1.jpg

 

Then create a measure to get the grand total.

Total = 
IF (
    MAX ( Table1[DuplicateRows] ) > 1,
    MAX ( Table1[Grand Total] ),
    SUM ( Table1[Grand Total] )
)

CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN_2.jpg

 

Best Regards,

Herbert

@v-haibl-msft  Do you have to add all the columns in the expression?  I have a similar situation but with many more columns.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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