Need row wise difference in table matrix

[bhuprakashs]

Hi Team,

I want to get row by row difference of value and want to show in table matrix. 

Input:

Product	Basic	Deluxe	Economy
A	4	6	5
B	9	5	1
C	8	1	7
D	5	2	5
E	10	3	6

Output:

Product	Basic	Deluxe	Economy
A	4	6	5
B	5	-1	-4
C	-1	-4	6
D	-3	1	-2
E	5	1	1

if you see the output table,
For Product A Row , I am showing same value because it is first row so no value to subtract.

For Product B Row , I want Product B - Product A 
For Product C Row , I want Product C - Product  B and So on.

Kindly help me how can I achieve this ? thank you in advance. 

[DataVitalizer]

Hi @bhuprakashs 

I suggest following these steps inside Power Query to achieve your goal:

• In Power Query, sort your table by Product.

• Add an Index column starting at 0.

• Duplicate the query as a reference, call it PrevTable. In PrevTable, add 1 to the Index (so row A=1, B=2, etc.).

• Merge the original table with PrevTable on Index = Index+1.

• Expand the merged columns (Basic, Deluxe, Economy) as previous values.

• Add custom columns:

BasicDiff = if [PrevTable.Basic] = null then [Basic] else [Basic] - [PrevTable.Basic],
DeluxeDiff = if [PrevTable.Deluxe] = null then [Deluxe] else [Deluxe] - [PrevTable.Deluxe],
EconomyDiff = if [PrevTable.Economy] = null then [Economy] else [Economy] - [PrevTable.Economy]

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

[bhuprakashs]

@DataVitalizer thanks for the help. but I am using live connection as a data source so can not make changes in dataset. any way solution with DAX ?

@amitchandak , Thanks Amit . I am checking your solution but I do not want to keep original columns in the table. Any other solution ?

[DataVitalizer]

I'm glad @amitchandak's solution worked for you.

[amitchandak · Accepted solution]

@bhuprakashs , Create a visual calculation like this and hide the original measure, assuming this a matrix. Else do for all three measures and hide original 

Calculation = if([Brand] ="Brand 1", [Net], [Net]- PREVIOUS([Net]))

Visual Calculations  RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

[bhuprakashs]

@amitchandak Sorry I got an option to hide the original measure column which is completely hidden from visual. Thanks Amit , visual calculation working properly for me.