PowerBI Matrix Table with finding difference between two column and display it

[pyramid1]

Hello expert, I need some help with Matrix.

Here is my sample data

Person	Year	Salary	Tax
P1	2021	$ 300.00	$   27.00
P2	2021	$ 400.00	$   56.00
P3	2021	$ 500.00	$   38.00
P1	2022	$ 370.00	$   40.00
P2	2022	$ 430.00	$   20.00
P3	2022	$ 478.00	$   20.00

Here is my desired output.

	Salary			Tax
Person	2021	2022	Diff	2021	2022	Diff
P1	$ 300.00	$ 370.00	$   70.00	$   27.00	$   40.00	$   13.00
P2	$ 400.00	$ 430.00	$   30.00	$   56.00	$   20.00	-$   36.00
P3	$ 500.00	$ 478.00	-$   22.00	$   38.00	$   20.00	-$   18.00

Output in png format (I tried to paste output in table format above but seems post is auto formatted): 

I tried to put this data in PowerBI matrix but seems unable to calculate measure to find difference between year and display it next to year. 

Any help would be appreciate.

[Anonymous · Accepted solution]

Hi @pyramid1 ，

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2)Click "transform data" to enter the power query editor, select the [Year] column and [Person] column, and click transpose. Click "Close and apply".

(3) We can create a measure. 

Diff = 
var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Person]=MAX('Table'[Person]) && 'Table'[Attribute]=MAX('Table'[Attribute]) && 'Table'[Year]=2022))
var _b=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Person]=MAX('Table'[Person]) && 'Table'[Attribute]=MAX('Table'[Attribute]) && 'Table'[Year]=2021))
var _d=FORMAT(_a-_b,"Fixed")
var _c=IF(_a-_b < 0, "-" & "$"& _d,"$" & _d)
return IF(SELECTEDVALUE('Table'[Year])=2021,BLANK(),_c)

(4) Then the result is as follows.

Due to the design of the power bi desktop, the matrix may not display exactly what you expect. You can create a shapes in the [Insert] pane and set its background color to white and no border to block the fields you don't want to display.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.