Difference between columns in matrix

[Jody]

I have a matrix in Power BI desktop that looks like this.  Column headings 2018, 2019, 2020 represent years and the values are a "count" of PersonID (as opposed to sum).

COMPANY	2018	2019	2020
A	595	570	651
B	1	2
C	17	11	9
D	455	541	531
E	125	106	101
F	2868	2678	2735

What I need is a column which calculates the difference between 2020 and 2019, ie. an excel formula of =2020-2019 with the result:

COMPANY	2018	2019	2020	DIFFERENCE
A	595	570	651	81
B	1	2		-2
C	17	11	9	-2
D	455	541	531	-10
E	125	106	101	-5
F	2868	2678	2735	57

and a growth rate, ie: =(2020-2019)/2019 shown as a percentage.  Is any of this possible in a matrix?

[Anonymous · Accepted solution]

@Jody 

Yes this is possible. If you have the data already formatted nicely in a table (i.e. you're not taking counts yet), you want to make sure that the blank or null in the "2020" column is replaced with a zero so it does not mess up your calculations. You can do this by highlighting the "2020" column, right clicking, and then clicking "replace values..." to replace null with 0. If you are getting these values in your matrix with the count function from a different table, you just need to make sure you wrap each element of these measure equations in a COUNT, DISTINCTCOUNT, or COUNTX function depending if you are getting your values with a regular count, distinct count, count with conditions, etc.

Then you can just add a measure with an equation like this for your first request:

DIFFERENCE = VALUES(Table[2020]) - VALUES(Table[2019])

And like this for your second request:

GrowthRate = DIVIDE([DIFFERENCE], VALUES(Table[2019]))

Note again that you may need to replace the VALUES function with some variation of COUNT if your original table does not look like the table you've provided. This is what my final table looks like after applying those functions to your table:

[Anonymous]

I have same problem, but I can't solve it.  I made matrix from table with values for places each year. I get Total in column to right but I need difference between column nr. 2 and 10 and ratio in next column.  I don't find solution for that.

My example in Matrix table

Place           2011       2012...........2019   Total(but I need difference)

East cost     10.605                  11.254    21.859 (649)

How do I solve this?

Best regards,

Jóhannes Finnur

[Anonymous]

@Jody 

I forgot to mention that to format the GrowthRate column as a percentage, you just need to click on the GrowthRate column in the Fields tab (on the right) and then on the Modeling tab on the top. Then you can select that you want your measure to be displayed as a percentage and how many decimal places you would like to see.

Hope this helps.

[Jody]

Thank you Emily, this has helped me enormously