Forum Discussion

AmandaLu's avatar
AmandaLu
Frequent Visitor
4 years ago
Solved

Calculate the difference between two columns in a matrix when each row is a measure

Hi,    I'm working on a matrix in Power BI that compares this fiscal years' sales, transactions, units, customerscount to last year's.  Each row is it's own calculated measure.  Is there any ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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"

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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"

AmandaLu's avatar
AmandaLu
Frequent Visitor
4 years ago

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!