Forum Discussion
Difference between 2 measures in matrix table
Please find the attached solution. The only diff is CDR Month 2. To me, it seems like either I forget to copy a line, or might be missing in the post.
Community ChampionAnonymous
It depends on how you have set up your model, how you are calculating the averages, and what is the result you are expecting.
If it is a direct substraction, try this:
1) create a lookup table with unique values for "Month": join this field with both your tables (month fields) in a one-to-many relationship.
2) set up you "Diff Table" using the month field from the lookup table and the measures.
Does it work?
If not please post sample data from both your tables in data format (not as an image)
Thanks Paul. Find below the data
Table A (DATA)
| YEAR | DATE | MONTH | BATCH | PRODCUT | WEIGHT | PRICE | Weighted |
| 2020 | 08-01-20 | 1 | 10823 | CAR | 3,000.000 | 2,820.00 | 8460000 |
| 2020 | 08-01-20 | 1 | 10826 | CAR | 3,000.000 | 2,820.00 | 8460000 |
| 2020 | 08-01-20 | 1 | 10962 | CAR | 3,000.000 | 2,800.00 | 8400000 |
| 2020 | 12-02-20 | 2 | 22798 | CAR | 3,000.000 | 2,500.00 | 7500000 |
| 2020 | 12-02-20 | 2 | 22845 | CAR | 3,000.000 | 2,500.00 | 7500000 |
| 2020 | 12-02-20 | 2 | 22846 | CAR | 3,000.000 | 2,500.00 | 7500000 |
| 2020 | 08-01-20 | 1 | 10697 | CDR | 3,000.000 | 2,460.00 | 7380000 |
| 2020 | 08-01-20 | 1 | 10698 | CDR | 3,000.000 | 2,460.00 | 7380000 |
| 2020 | 08-01-20 | 1 | 10699 | CDR | 3,000.000 | 2,460.00 | 7380000 |
| 2020 | 08-01-20 | 1 | 10735 | CDR | 3,000.000 | 2,400.00 | 7200000 |
| 2020 | 08-01-20 | 1 | 10736 | CDR | 3,000.000 | 2,460.00 | 7380000 |
| 2020 | 12-02-20 | 2 | 18426 | CDR | 3,000.000 | 2,270.00 | 6810000 |
| 2020 | 12-02-20 | 2 | 18427 | CDR | 3,000.000 | 2,270.00 | 6810000 |
| 2020 | 12-02-20 | 2 | 60 | CDR | 2,720.000 | 2,440.00 | 6636800 |
Table A Weighted price:
Based on the above measure, my Matrix table shown as
| MONTH | CAR | CDR | Total |
| 1 | 2,813.33 | 2,448.00 | 2,585.00 |
| 2 | 2,500.00 | 2,394.30 | 2,424.67 |
| Total | 2,656.67 | 2,421.15 | 2,494.23 |
Next steps in cont...
cont... 2
Table B (DATA)
YEAR
DATE
MONTH
BATCH
PRODCUT
WEIGHT
PRICE
Weighted
2019
04-01-19
1
11263
CAR
3,000.000
2,464
7392000
2019
04-01-19
1
11264
CAR
3,000.000
2,464
7392000
2019
04-01-19
1
11265
CAR
3,000.000
2,464
7392000
2019
09-01-19
1
11958
CDR
3,000.000
2,328
6984000
2019
09-01-19
1
11980
CDR
3,000.000
2,347
7041000
2019
09-01-19
1
12016
CDR
3,000.000
2,367
7101000
2019
09-01-19
1
12017
CDR
3,000.000
2,357
7071000
2019
14-02-19
2
7642
CAR
3,000.000
2,386
7158000
2019
14-02-19
2
7643
CAR
3,000.000
2,386
7158000
2019
14-02-19
2
14034
CAR
3,000.000
2,280
6840000
2019
14-02-19
2
14035
CAR
3,000.000
2,280
6840000
2019
14-02-19
2
14037
CAR
3,000.000
2,280
6840000
2019
21-02-19
2
12066
CAR
3,000.000
2,280
6840000
2019
27-02-19
2
17440
CDR
2,996.000
1,989
5959044
2019
27-02-19
2
17441
CDR
2,996.000
1,989
5959044
Table B Weighted price:
WAvg 2019 Price =
VAR __CATEGORY_VALUES = VALUES('2019'[WEIGHT])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(
SUM('2019'[WEIGHT])
* AVERAGE('2019'[PRICE])
)
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('2019'[WEIGHT]))
)
)
Based on the 2nd table measure, my Matrix table shown as
MONTH
CAR
CDR
Total
1
2,464.00
2,349.75
2,398.71
2
2,315.33
1,989.00
2,152.22
Total
2,364.89
2,133.36
2,243.05
Then, my 3 measure was to see the difference.
diff3 = '2020'[WAvg Price] - '2019'[WAvg 2019 Price]
Output received was
MONTH
CAR
CDR
Total
1
570.28
204.95
341.95
2
256.95
151.25
181.62
Total
413.61
172.83
251.18
whereas expected result was
MONTH
CAR
CDR
Total
1
349.33
98.25
186.29
2
184.67
405.30
272.45
Total
291.78
287.79
251.18
Trust this input is sufficient, looking forward for your reply.
- amitchandak
Super User
Please find the attached solution. The only diff is CDR Month 2. To me, it seems like either I forget to copy a line, or might be missing in the post.