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.

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.

SebastianKief's avatar
SebastianKief
New Member
7 years ago

that worked perfectly for me - thanks a lot for the help :)

 

But I am still struggling to  shrink the columns with the 0 values.

It would be great if someone could help me with that.

 

Many thanks in advance

  • Pfoerster's avatar
    Pfoerster
    Helper III
    6 years ago

    Hello,

    I have build up this table in Power BI:

    And the aim should be to get something like this:

    So, I need the rows with the differences. How can I do it? In the Source Table, each Value is one column, and the Scenario is also in one single column. Is there any possibility to create this kind of differences?