Difference between two columns or rows

[Raul]

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.

[jeoosma]

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.

[Greg_Deckler]

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]

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

[dkay84_PowerBI · Accepted solution]

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.

[Brennan42]

I would move the BLANK (), to after the IF so that any nulls are 0.00

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

[erdinair1977]

Is there a way to adjust the "Difference" formula above to calculate across rows when the value is blank?  This formula works for what I need, but I have blanks in my subtotals and it does not work in those situations.  Your help would be greatly appreciated!

I'm expecting 1 - (blank) = 1 (Target Remaining) and (blank) - 1 = -1 (Target Remaining).

[SebastianKief]

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]

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?

[Raul]

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?

[Raul]

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

[dkay84_PowerBI]

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

[Raul]

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.

[Anonymous]

Hi did you fix this issue?

[Greg_Deckler]

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)