Forum Discussion
Calculate the difference between two columns in a matrix when each row is a measure
Hi AmandaLu
Yes that can be done utilizing the column subtotal. For exampleMeasure2 = VAR CurrentValue = [Measure1] VAR MaxYear = MAX ( 'Date'[Year] ) VAR MinYear = MIN ( 'Date'[Year] ) VAR MaxYearValue = CALCULATE ( [Measure1], 'Date'[Year] = MaxYear ) VAR MinYearValue = CALCULATE ( [Measure1], 'Date'[Year] = MinYear ) RETURN IF ( HASONEVALUE ( 'Date'[Year] ), CurrentValue, MaxYearValue - MinYearValue )And you can manually change the name from "Total" to "Difference"
Hi AmandaLu
Yes that can be done utilizing the column subtotal. For example
Measure2 =
VAR CurrentValue = [Measure1]
VAR MaxYear =
MAX ( 'Date'[Year] )
VAR MinYear =
MIN ( 'Date'[Year] )
VAR MaxYearValue =
CALCULATE ( [Measure1], 'Date'[Year] = MaxYear )
VAR MinYearValue =
CALCULATE ( [Measure1], 'Date'[Year] = MinYear )
RETURN
IF ( HASONEVALUE ( 'Date'[Year] ), CurrentValue, MaxYearValue - MinYearValue )And you can manually change the name from "Total" to "Difference"
Hi tamerj1 ,
Thank you so much for your reply!
I added the subtotal column, but I cannot edit the power query of that column. Could you help?
Also, do you know how to add the % diff column (which is (this year's measure - last year's measure)/last year's measure), we can only add one column right?
I have been struggling with this problem for several days. Thank you again for your help!
That would be great! Are you able to join the meeting at 1 pm today? or just let me know what time is convenient for you.
Join Zoom Meeting
https://zoom.us/j/96020170183?pwd=YzBFWVhhdXRhcHdHT0Z2aHc1clRTdz09
Meeting ID: 960 2017 0183
Passcode: yLZ8fdCan we connect now?
Hello, did you ever find the solution to your problem? I am interested to find out what the resolution was as I'm facing a similar issue.
