cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
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

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:

 

Capture.PNG

Oh and change the formatting/style for the measures as desired.

View solution in original post

12 REPLIES 12
Helper II
Helper II

bar.jpg

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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:

 

Capture.PNG

Oh and change the formatting/style for the measures as desired.

View solution in original post

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

Captures.PNG

Hello,

I have build up this table in Power BI:

Table.JPG

And the aim should be to get something like this:

Diff.JPG

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?

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?

 

Captura.JPG

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

Captura.JPG

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

Hello @dkay84_PowerBI!

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

Captura.JPG

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

Captura.JPG

 

 

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

Captura.JPG

Is there any solution to avoid this?

Thank you.

Hi did you fix this issue?

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)

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors