Forum Discussion
Need row wise difference in table matrix
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
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 💡
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 ?
I'm glad amitchandak's solution worked for you.