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

WEST

NORTH

2015

4.320

3.200

5.000

2016

5.200

2.000

7.500

Difference

880

-1.200

2.500

 

With a filter visualization where select the two years to compare.

How I calculate this difference?

 

Similary, I have the following:

 

YEAR

 

 

 

ZONE

2015

2016

Difference

%Inc

EAST

4.320

5.200

880

20%

WEST

3.200

2.000

-1.200

-38%

NORTH

5.000

7.500

2.500

50%

 

How I calculate this difference and the % increase?

Thank you.

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.

14 Replies

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

  • jeoosma's avatar
    jeoosma
    Helper II
    8 years ago

    Hi. I would calcolate the difference between the import of 2017 vs 2016. I have inserted a column with a new measure in this way:

    Difference = IF (HASONEVALUE ('CdeAuftragK'[Anno]);BLANK (); CALCULATE (SUM ( 'CdeAuftragK'[ValoreVendita] ); FILTER ( 'CdeAuftragK'; 'CdeAuftragK'[Anno] = MAX ( 'CdeAuftragK'[Anno] ) ))- CALCULATE (SUM ( 'CdeAuftragK'[ValoreVendita]);FILTER ( 'CdeAuftragK';'CdeAuftragK'[Anno] = MIN ( 'CdeAuftragK'[Anno] ) )))

    as I see in the reply. The value in the column is wrong but I think is my interpretation of command. It likes on lines instead of columns. How Can I resolve? Thank you.