cancel
Showing results for
Did you mean:
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

Re: Difference between two columns or rows

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.

11 REPLIES 11
Super User

Re: Difference between two columns or rows

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?

Proud to be a Datanaut!

Member

Re: Difference between two columns or rows

Hi smoupre,

This is the sample data:

 ID ZONE DATEPHYS AMOUNT YEAR 1 EAST 01/02/2015 3.000 2015 2 WEST 01/02/2015 1.000 2015 3 NORTH 01/02/2015 2.500 2015 4 EAST 01/03/2015 1.320 2015 5 WEST 01/03/2015 2.200 2015 6 NORTH 01/03/2015 2.500 2015 7 EAST 01/02/2016 2.000 2016 8 WEST 01/02/2016 1.000 2016 9 NORTH 01/02/2016 2.000 2016 10 EAST 01/03/2016 3.200 2016 11 WEST 01/03/2016 1.000 2016 12 NORTH 01/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

Super User

Re: Difference between two columns or rows

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)```

Proud to be a Datanaut!

Member

Re: Difference between two columns or rows

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

New Contributor

Re: Difference between two columns or rows

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.

New Contributor

Re: Difference between two columns or rows

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

Member

Re: Difference between two columns or rows

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?

Member

Re: Difference between two columns or rows

Hello @dkay84_PowerBI!

I have one problem with shrink the columns in a matrix visualization. I create this visualization:

When I show the matrix like this, shrink the columns with values to 0:

and I refresh the data, the shrink columns are displayed again.

Is there any solution to avoid this?

Thank you.

Regular Visitor

Re: Difference between two columns or rows

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.