Measure: SUM that is conditional based on a value in another column

[AliceW]

Hi guys,

Sounds simple, yet it's complicated. Any help will be very much appreciated.

So, I have this table where the total is the problem:

Opp ID      Rep Name     Amount

1                Trevor           100

1                John              100

2                Alex               50

TOTAL                             250 

Now, notice that Opp ID 1 is present twice, as the field Rep Name is different. 

I'd like to have the Total Amount taking into account Opp ID 1 only once; in this example, I'd like the total to be 150 instead of 250.

Any thoughts on how to proceed, please?

Thanks in advance,

Alice

[Sean · Accepted solution]

@AliceW

You've provided very limited sample data and have not specified what the outcome should be if the Amount is different for the same Opp Id so I decided to go with AVERAGE...

Measure =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[Opp ID], "Amt", AVERAGE ( 'Table'[Amount] ) ),
    [Amt]
)

This should give you the average per Opp ID in case the amounts are different. (and we'll use this number in calculating the total)

Obviously you can change to MAX or MIN depending on your actual requirements and data.

Hope this helps! 

[AliceW]

Thanks, Sean! Yes, the Opp amount should be the same, but I'll add MAX just to be on the safe side. Thanks again!!