How to sum column based on distinct values from another

[GinjaNinja29]

Hello - these are samples of the relevant information in the table for which I am attempting to sum the quantity, based on the unique ID.

If the ID is found in the table more than once, I only need one line where the Quantity is summed.  Since I will be using this to calculate averages as well, it would be ideal if the result column simply displayed a blank (instead of 0) for the Result.

Thank You!

Original Table:

ID	Quantity
1	1
1	2
1	4
2	1
3	7
3	3
2	5

Calculated Measure:

ID	Quantity
1	7
2	6
3	10

[jwessel]

Based on the table supplied with ID and Quantity, what would be the way to just obtain the maximum quantity value for each ID in a vis ?  (PowerBI newby here) 😁

[amitchandak]

@GinjaNinja29 ,

For Avg you do not need to that, You can so like Avg of sum

AverageX(summarize(Table,Table[ID],"_1",sum(Table[Quantity])),[_1])

or
AverageX(values(Table[ID]),sum(Table[Quantity]))

[GinjaNinja29]

Thank you - I should be more clear.

In one of my visuals, I will be also using this field to count is not blank. 

This field will be used to count (is not blank), Sum, Average, and count if >3

[v-easonf-msft · Accepted solution]

Hi , @GinjaNinja29 

You may need to create calculated columns as below:

Count 1 = CALCULATE(COUNT('Original Table'[ID]),ALLEXCEPT('Original Table','Original Table'[ID]))

Quantity1 = IF('Original Table'[Count 1]>1,'Original Table'[Quantity],BLANK())

The result will show as below:

You also can try to create measure like this:

Quantity 2 = 
var a= COUNT('Original Table'[ID])
return IF(a>1,CALCULATE(SUM('Original Table'[Quantity])),BLANK())

In table visual , make sure the  fileld "id" show items with no data

Here is a sample.

pbix attached 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.