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

 

Any help would be greatly appreciated.

 

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"

9 Replies

  • 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 

     

     

  • 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!