CALCULATIONS BASED ON A DISTINCT VALUE IN A COLUMN

[Rsanjuan]

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

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

[v-haibl-msft · Accepted solution]

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

Then create a measure to get the grand total.

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

Best Regards,

Herbert

[Rsanjuan]

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