cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 !

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

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()
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

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

Highlighted
Anonymous
Not applicable

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

6 REPLIES 6
Highlighted
Super User I
Super User I

Re: Calculate the difference between two columns in matrix

can you share the layout of underlying data table?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Frequent Visitor

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 I
Super User I

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()
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

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

Highlighted
Anonymous
Not applicable

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

Highlighted
Frequent Visitor

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 ?

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors