Splitting values where keys matching

[Sergei8000]

I have two tables. In table 1 there is about 800 repeating key values. In table 100 unique key values and 20 columns with data values.

I want output where each matching value splitted by number of matches.

Table 1

id

A

B

A

C

A

A

C

Table 2
unique_id	Col1	Col2	Col3
A	400	100	20
B	200		800
C	600	800

output
id	Col1	Col2	Col3
A	100	25	5
B	200		800
A	100	25	5
C	300	400
A	100	25	5
A	100	25	5
C	300	400

How to achieve this?

[Dangar332 · Accepted solution]

hi, @Sergei8000 

try below

Column 1= 
 var a = CALCULATE(SUM('Table 2'[Col1]))
 var b = CALCULATE(COUNT('Table 1'[id]))
  return
  DIVIDE(a,b)

repeat for all column

[v-tangjie-msft · Accepted solution]

Hi @Sergei8000 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Col 1 = 
 var a = CALCULATE(SUM('Table 2'[Col1]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id])))
 var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id])))
  return
  DIVIDE(a,b)

Col 2 = 
 var a = CALCULATE(SUM('Table 2'[Col2]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id])))
 var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id])))
  return
  DIVIDE(a,b)

Col 3 = 
 var a = CALCULATE(SUM('Table 2'[Col3]),FILTER('Table 2','Table 2'[unique_id]=EARLIER('Table 1'[id])))
 var b = COUNTROWS(FILTER('Table 1','Table 1'[id]=EARLIER('Table 1'[id])))
  return
  DIVIDE(a,b)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Dangar332 · Accepted solution]

hi, @Sergei8000 

try below

Column 1= 
 var a = CALCULATE(SUM('Table 2'[Col1]))
 var b = CALCULATE(COUNT('Table 1'[id]))
  return
  DIVIDE(a,b)

repeat for all column