Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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.
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.
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
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |