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

[AmandaLu]

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 possible way to create a difference column? 

Any help would be greatly appreciated.

[AmandaLu]

Thanks for @tamerj1 's solution, I fixed this problem. 

There are the steps that we take to solve this problem:

Step 1: add two more rows named "Difference", and "Difference %" to the original table (so besides the year column, we have two more columns)

Step 2: use DAX to calculate each measure before moving them to the values (switch values to rows)

For example, the DAX for customer measure is:

Customers Measure =
VAR NormalValue =
    SUM ( Customer_Merged[Customers] )
VAR Value2022 =
    CALCULATE (
        SUM ( Customer_Merged[Customers] ),
        Customer_Merged[Transaction_FiscalYear] = "2022",
        ALLEXCEPT (
            Customer_Merged,
            Customer_Merged[Customer_TenureGroup_TTM],
            Customer_Merged[Transaction_FiscalQuarter]
        )
    )
VAR Value2023 =
    CALCULATE (
        SUM ( Customer_Merged[Customers] ),
        Customer_Merged[Transaction_FiscalYear] = "2023",
        ALLEXCEPT (
            Customer_Merged,
            Customer_Merged[Customer_TenureGroup_TTM],
            Customer_Merged[Transaction_FiscalQuarter]
        )
    )
VAR Difference = Value2023 - Value2022
VAR DifferencePercent =
    FORMAT ( DIVIDE ( Difference, Value2022 ), "Percent" )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Customer_Merged[Transaction_FiscalYear] ) = "Difference", Difference,
        SELECTEDVALUE ( Customer_Merged[Transaction_FiscalYear] ) = "% Difference", DifferencePercent,
        NormalValue
    )

Step 3: move this customer measure to values and do the same thing for all the measures 

[AmandaLu]

Thank you @tamerj1 ! 

[tamerj1 · Accepted solution]

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]

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!

[WorkerBeeJohn]

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.

[tamerj1]

Hi @AmandaLu 

Ok. Can you join a zoom meeting?

[AmandaLu]

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: yLZ8fd

[tamerj1]

Can we connect now?

[AmandaLu]

Yes!