cancel
Showing results for
Did you mean: Helper V

## 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 Microsoft

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.

12 REPLIES 12 Helper II 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. Super User IV

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?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Helper V

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 Microsoft

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 Member

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.  Helper III

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? Helper V

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?  Helper V

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

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

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. Helper I

Hi did you fix this issue? Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  