Sum of values based on distinct values in other column

[Rsanjuan]

Hello,

Let's say there are two columns out of 20. 

 

What's the correct measure syntax to sum up the Bid Value for each distinct Bid Number so that the sum would equal 50?

Thanks!

[Vvelarde]

@Rsanjuan

Hi Try with this:

BidValue per BidNumber =
SUMX(
 VALUES('Bids'[BidNumber]);
 CALCULATE(AVERAGE('Bids'[BidValue]))
)

[Rsanjuan]

@Vvelarde

I'm not sure if it's correct, it actually created an overall sum that is more.  Here's the actual expression I used:

TotalBidValue = SumX(Values(Master[Bid_Number__c]),CALCULATE(Average(Master[Bid Value])))

If I were to sum up the numbers in the chart above, the total would be 70 if we are not taking into account the duplicate bid numbers, but since we're adding up only the bid value when the bid numbers are distinct, it should be 50.

Did I do something wrong?

[v-yulgu-msft · Accepted solution]

Hi @Rsanjuan,

In your scenario, you can create a calculate column first:

Column 1 =
IF (
    CALCULATE ( COUNTAX ( Master, Master[Bid Number] ) ) = 1,
    Master[Bit Value],
    Master[Bit Value] / ( CALCULATE ( COUNTAX ( Master, Master[Bid Number] ) ) )
)

Then, based on this calculated column, sum its total via measure:

Measure1 = SUM(Master[Column 1])

Best regards,
Yuliana Gu