Forum Discussion
Need row wise difference in table matrix
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.
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
5 Replies
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=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8Lamitchandak 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.
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.
