Forum Discussion
Difference between 2 measures in matrix table
Hi All - I am beginner and started using PBI very recently. I am trying show 2 tables with weighted average measure and later trying to show the difference between 2 tables. The difference is not working, as it is not subtracting cell by cell by, rather Table A cell - Table B total. Please help me. My difference measure is ( diff = '2020'[WAvg Price] - '2019 '[WAvg 2019 Price]
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.
5 Replies
Please share the formula and some sample data and sample output.
With a date table and Datesytd or totalytd, YTD (or this year last year can be found like given below) and then take diff of two measures.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Anonymous
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:
WAvg Price =VAR __CATEGORY_VALUES = VALUES('2020'[WEIGHT])RETURNDIVIDE(SUMX(KEEPFILTERS(__CATEGORY_VALUES),CALCULATE(SUM('2020'[WEIGHT])* AVERAGE('2020'[PRICE]))),SUMX(KEEPFILTERS(__CATEGORY_VALUES),CALCULATE(SUM('2020'[WEIGHT]))))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.
