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.
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.
Super UserPlease 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.