## Desktop

shibabrata27 Frequent Visitor
Posts: 5
Registered: ‎02-08-2019 # Calculate the difference between two columns in matrix

Hi, I have below below data in Matrix View
Day0   Day1   Day2   Day3
Amount 200     230      195      220

I want one more row in matrix view as

% Diff from prev value, final output I want like

Day0     Day1     Day2     Day3
Amount 200       230       195       220
% Diff                15.0%    -15.2%   12.8%

Can anyone suggest how easily I can achieve this.

In other Visualization tool it was someting like get previous value and do a % calculation. In Power BI , I am not able to proceed.

NOTE: Below view I want in Matrix Visualization in Power BI

Thanks in Advance !

Accepted Solutions
Highlighted Super User
Posts: 889
Registered: ‎06-23-2016 ## Re: Calculate the difference between two columns in matrix

Day has to be numeric for this
as you want to have Measures in rows you actually will need a new table for that, you can  create one in DAX

`RowLabels = UNION(ROW("Column","Amount"),ROW("Column","Difference"))`

then this measure will work

```Measure =
VAR __Amount = SUM('Table'[Amount])
VAR __DayIndex = MAX('Table'[Day])
VAR __CurrentDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex)
VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)
VAR __Difference = DIVIDE(__CurrentDay-__PreviousDay,__PreviousDay)
VAR __RowLabel = SELECTEDVALUE(RowLabels[Column])
RETURN
SWITCH(__RowLabel,
"Amount", __Amount,
"Difference", __Difference,
BLANK()
)```
shibabrata27 Frequent Visitor
Posts: 5
Registered: ‎02-08-2019 ## Re: Calculate the difference between two columns in matrix

Here DayIndex-1 is throwing error , I think for the very first index its giving -ve index which is not there.

`VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)`

Kindly share how to handle that because PreviousDay value for first index i want as blank and same for difference column as well. For fisrt Day difference should come as blank

vanguripavan Regular Visitor
Posts: 39
Registered: ‎12-07-2016 ## Re: Calculate the difference between two columns in matrix

Hi,

Below is the solution screenshot for your requirement. Click Here to download the sample application.

Regards,

Pavan Vanguri.

All Replies Super User
Posts: 889
Registered: ‎06-23-2016

## Re: Calculate the difference between two columns in matrix

can you share the layout of underlying data table?

shibabrata27 Frequent Visitor
Posts: 5
Registered: ‎02-08-2019

## Re: Calculate the difference between two columns in matrix

Hi I have data format something like this.

User     Day    Amount

U1        Day0    1000

U2       Day2      3000

U2        Day1     234

U3        Day0     345

Highlighted Super User
Posts: 889
Registered: ‎06-23-2016 ## Re: Calculate the difference between two columns in matrix

Day has to be numeric for this
as you want to have Measures in rows you actually will need a new table for that, you can  create one in DAX

`RowLabels = UNION(ROW("Column","Amount"),ROW("Column","Difference"))`

then this measure will work

```Measure =
VAR __Amount = SUM('Table'[Amount])
VAR __DayIndex = MAX('Table'[Day])
VAR __CurrentDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex)
VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)
VAR __Difference = DIVIDE(__CurrentDay-__PreviousDay,__PreviousDay)
VAR __RowLabel = SELECTEDVALUE(RowLabels[Column])
RETURN
SWITCH(__RowLabel,
"Amount", __Amount,
"Difference", __Difference,
BLANK()
)```
shibabrata27 Frequent Visitor
Posts: 5
Registered: ‎02-08-2019 ## Re: Calculate the difference between two columns in matrix

Here DayIndex-1 is throwing error , I think for the very first index its giving -ve index which is not there.

`VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)`

Kindly share how to handle that because PreviousDay value for first index i want as blank and same for difference column as well. For fisrt Day difference should come as blank

vanguripavan Regular Visitor
Posts: 39
Registered: ‎12-07-2016 ## Re: Calculate the difference between two columns in matrix

Hi,

Below is the solution screenshot for your requirement. Click Here to download the sample application.

Regards,

Pavan Vanguri.

shibabrata27 Frequent Visitor
Posts: 5
Registered: ‎02-08-2019

## Re: Calculate the difference between two columns in matrix

Thanks,

I am able to get the %diff value when I am keeping date filed in column since e have used Max Date concept here. But how i can Month name, quarter , Fin Year etc in a hierarchy and put that in column. And based on lower drill down value ( in this case it is month ) I will get % diff . How a dynamic calculation we can do ?