Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have
Table 1
Name | Year | Subject | Weightage |
Alan | 2016 | CCA | 0.1 |
Donal | 2016 | PPP | 0.2 |
Dhoni | 2017 | SCA | 0.15 |
Ram | 2018 | SSS | 0.12 |
Siva | 2018 | SSS | 0.18 |
Table 2
Name | Year | Spend |
Alan | 2016 | 154 |
Donal | 2017 | 125 |
Dhoni | 2017 | 676 |
Ram | 2017 | 457 |
Donal | 2016 | 458 |
Ram | 2018 | 784 |
When there is a match between table 1 & 2 row wise, for eg: Alan 2016 in the 1st row of Table 1 match with Alan 2016 of Table 2 1st row. if matched the I want a calculated column in Table 2 by multiplying Spend with weightage
The result shall be in Table 2 as
Name | Year | Spend | Weighted Spend |
Alan | 2016 | 154 | 15.4 |
Donal | 2017 | 125 | 0 |
Dhoni | 2017 | 676 | 101.4 |
Ram | 2017 | 457 | 0 |
Donal | 2016 | 458 | 91.6 |
Ram | 2018 | 784 | 94.08 |
Please help & thanks
Solved! Go to Solution.
Hi @mahra-in
Please add the following calculated column to your 'Table 2'
Weighted Spend = 'Table 2'[Spend] * CALCULATE( MIN('Table 1'[Weightage]), FILTER( 'Table 1', 'Table 1'[Name] = EARLIER('Table 2'[Name]) && 'Table 1'[Year] = EARLIER('Table 2'[Year]) ) )
Hi @mahra-in
Please add the following calculated column to your 'Table 2'
Weighted Spend = 'Table 2'[Spend] * CALCULATE( MIN('Table 1'[Weightage]), FILTER( 'Table 1', 'Table 1'[Name] = EARLIER('Table 2'[Name]) && 'Table 1'[Year] = EARLIER('Table 2'[Year]) ) )
thank u
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |