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.
Solved! Go to 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.
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!
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
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!
I have corrected the measure and solved the error but the visual result is not as expected. Any ideas?
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.
You need to put Zone in the rows, Year in the column, and Amount, Difference and %Inc as values.
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?
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.
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.