Forum Discussion

Raul's avatar
Raul
Post Patron
9 years ago
Solved

Difference between two columns or rows

Hello everybody, I need to find the difference between two columns or two rows within a table or matrix of values. I have the following situation:   YEAR ZONE       EAST W...
Need Help
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    Create a measure called "Difference"

     

    Difference =
IF (
    HASONEVALUE ( 'Table'[YEAR] ),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[AMOUNT] ),
        FILTER ( 'Table', 'Table'[YEAR] = MAX ( 'Table'[YEAR] ) )
    )
        - CALCULATE (
            SUM ( 'Table'[AMOUNT] ),
            FILTER ( 'Table', 'Table'[YEAR] = MIN ( 'Table'[YEAR] ) )
        )
)

    Then a measure called "%Inc"

     

    %Inc =
DIVIDE (
    [Difference],
    CALCULATE (
        SUM ( 'Table'[AMOUNT] ),
        FILTER ( 'Table', 'Table'[YEAR] = MIN ( 'Table'[YEAR] ) )
    )
)

    Then just shrink the columns in your matrix that have no data so they don't show up, and make sure to turn the row totals on:

     

    Oh and change the formatting/style for the measures as desired.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Perhaps something along the lines of:

 

Measure = CALCULATE(SUM([Column1]),FILTER(Table,[Year]=MAX([Year])) - CALCULATE(SUM([Column1]),FILTER(Table,[Year]=MIN([Year]))

Specifics will depend on your data. Can you post some raw, sample data?

  • Raul's avatar
    Raul
    Post Patron
    9 years ago

    Hi smoupre,

    This is the sample data:

    IDZONEDATEPHYSAMOUNTYEAR
    1EAST01/02/2015 3.000   2015
    2WEST01/02/2015 1.000   2015
    3NORTH01/02/2015 2.500   2015
    4EAST01/03/2015 1.320   2015
    5WEST01/03/2015 2.200   2015
    6NORTH01/03/2015 2.500   2015
    7EAST01/02/2016 2.000   2016
    8WEST01/02/2016 1.000   2016
    9NORTH01/02/2016 2.000   2016
    10EAST01/03/2016 3.200   2016
    11WEST01/03/2016 1.000   2016
    12NORTH01/03/2016 5.500   2016

     

    I create the new measure like this:

    Difference = CALCULATE(SUM([AMOUNT]);FILTER(Tabla1;[Year]=MAX([Year])) - CALCULATE(SUM([AMOUNT]);FILTER(Tabla1;[Year]=MIN([Year]))))

     

    But it throw an error in the FILTER function. What is wrong?

    Thanks

    • dkay84_PowerBI's avatar
      dkay84_PowerBI
      Microsoft Employee
      9 years ago

      Create a measure called "Difference"

       

      Difference =
IF (
    HASONEVALUE ( 'Table'[YEAR] ),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[AMOUNT] ),
        FILTER ( 'Table', 'Table'[YEAR] = MAX ( 'Table'[YEAR] ) )
    )
        - CALCULATE (
            SUM ( 'Table'[AMOUNT] ),
            FILTER ( 'Table', 'Table'[YEAR] = MIN ( 'Table'[YEAR] ) )
        )
)

      Then a measure called "%Inc"

       

      %Inc =
DIVIDE (
    [Difference],
    CALCULATE (
        SUM ( 'Table'[AMOUNT] ),
        FILTER ( 'Table', 'Table'[YEAR] = MIN ( 'Table'[YEAR] ) )
    )
)

      Then just shrink the columns in your matrix that have no data so they don't show up, and make sure to turn the row totals on:

       

      Oh and change the formatting/style for the measures as desired.

      • Raul's avatar
        Raul
        Post Patron
        9 years ago

        Perfect, this is the answer: I have to  shrink the columns in mymatrix that have no data.

         

        But the first visualization is not posible to do the same. Any suggestion?

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Try this:

       

      Difference = var MaxYear = MAX(Zones[YEAR])
var MinYear = MIN(Zones[YEAR])
RETURN CALCULATE(SUM([AMOUNT]),Zones[YEAR]=MaxYear) - CALCULATE(SUM([AMOUNT]),Zones[YEAR]=MinYear)
    • Raul's avatar
      Raul
      Post Patron
      9 years ago

      I have corrected the measure and solved the error but the visual result is not as expected. Any ideas?

      • dkay84_PowerBI's avatar
        dkay84_PowerBI
        Microsoft Employee
        9 years ago

        You need to put Zone in the rows, Year in the column, and Amount, Difference and %Inc as values.