Reply
Frequent Visitor
Posts: 5
Registered: ‎02-08-2019
Accepted Solution

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
Super User
Posts: 866
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()
)

View solution in original post

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

View solution in original post

Regular Visitor
Posts: 25
Registered: ‎12-07-2016

Re: Calculate the difference between two columns in matrix

Hi,

 

Below is the solution screenshot for your requirement.


Community Question - shibabrata27.png

 

 

 

 

 

 

 

 

Click Here to download the sample application.

Hope it help you.

 

Regards,

Pavan Vanguri.

 

View solution in original post


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

Re: Calculate the difference between two columns in matrix

can you share the layout of underlying data table?

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

 

Super User
Posts: 866
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()
)
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

Regular Visitor
Posts: 25
Registered: ‎12-07-2016

Re: Calculate the difference between two columns in matrix

Hi,

 

Below is the solution screenshot for your requirement.


Community Question - shibabrata27.png

 

 

 

 

 

 

 

 

Click Here to download the sample application.

Hope it help you.

 

Regards,

Pavan Vanguri.

 

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 ?