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.
Hi all,
I have two tables in PBI.
First,
ID | month | Actual |
1000001 | JAN | 131.8759 |
1000001 | FEB | 201.3989 |
1000001 | MAR | 46.79667 |
1000001 | APR | 362.4535 |
1000001 | MAY | 981.3332 |
1000001 | JUN | 724.6066 |
1000001 | JUL | 544.9559 |
1000001 | AUG | 483.6966 |
1000001 | SEP | 566.0321 |
1000001 | OCT | 745.6872 |
1000001 | NOV | 691.189 |
1000001 | DEC | 720.8833 |
1000002 | JAN | 539.6378 |
1000002 | FEB | 90.21495 |
1000002 | MAR | 581.3458 |
1000002 | APR | 696.0652 |
1000002 | MAY | 311.4853 |
1000002 | JUN | 258.6118 |
1000002 | JUL | 859.9209 |
1000002 | AUG | 662.226 |
1000002 | SEP | 642.5379 |
1000002 | OCT | 683.1684 |
1000002 | NOV | 146.0456 |
1000002 | DEC | 378.2254 |
Second,
Actual milled | month | Value |
Actual Tonnes Milled | JAN | 86160.95 |
Actual Tonnes Milled | FEB | 4192.094 |
Actual Tonnes Milled | MAR | 10359.42 |
Actual Tonnes Milled | APR | 85196.02 |
Actual Tonnes Milled | MAY | 11880.71 |
Actual Tonnes Milled | JUN | 44139.87 |
Actual Tonnes Milled | JUL | 70186.39 |
Actual Tonnes Milled | AUG | 79722.18 |
Actual Tonnes Milled | SEP | 56502.47 |
Actual Tonnes Milled | OCT | 7054.797 |
Actual Tonnes Milled | NOV | 110280.9 |
Actual Tonnes Milled | DEC | 70083.1 |
And I want to add to the first table a column or write a DAX to divide JAN value on the second table by JAN value of the fist table for every ID.
Thank you in advance.
Solved! Go to Solution.
Step1: Go to "Manage Relationships" and add one for month. (See below)
Step2: Add column to table1
Column = RELATED(table2[Value])
Step3: Add measure to table1
Measure = CALCULATE(SUM(table1[Column])/SUM(table1[Actual]))
Result:
Thank you! Everything worked perfectly.
Step1: Go to "Manage Relationships" and add one for month. (See below)
Step2: Add column to table1
Column = RELATED(table2[Value])
Step3: Add measure to table1
Measure = CALCULATE(SUM(table1[Column])/SUM(table1[Actual]))
Result:
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |