Unique Sum DAX

[AB1977]

Kia ora

The picture below is a snippet of my data set.

I want to be able to sum the second column based on the data in the first.

I want to sum column 2 for each of the unique sets of data in column 1.

I.e. the sum for 238329 would be 12 and for 238330 would be 12 and so on.

This however is only the start. The actual answer I need is 3 not 12. The second stage is to take the 12 and divide by the count of the 238329's (12/4 in this case).

Hope this is clear. The snip of data above has bben sorted for illustration purposes. The actual data could be in any order and there could be any number of matching data numbers in column 1 rather than the uniform 4 of each show above.

Many thanks in advanc for any help.

[v-jiascu-msft]

Hi @AB1977,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[vcastello · Accepted solution]

Hi AB1977,

Assuming your dataset has a table named "Table1" which first column is named "Column1" and second column is named "Column2" ...

1.- Create a new measure called "Total Column2" with the following DAX code: SUM(Table1[Column2]). 
2.- Create a new measure called "Total Column1" with the following DAX code: COUNTA(Table1[Column1]).
3.- Create a new measure called "Total" with the following DAX code: DIVIDE([Total Column2];[Total Column1]).

4.- Create a new visual (table) with Column1 (ID) on rows and each of the measures you've created on columns

That should work

Vicente

[erik_tarnvik]

Or, since the function described is essentially an average, create a measure AverageC2 = AVERAGE(Table1[Column2]) and create a visual with Column 1 in the first column and this measure in the second.