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"

AmandaLu's avatar
AmandaLu
Frequent Visitor
4 years ago

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