cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shibabrata27 Frequent Visitor
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
Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

shibabrata27 Frequent Visitor
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

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.

 

6 REPLIES 6
Highlighted
Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

shibabrata27 Frequent Visitor
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

 

Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

shibabrata27 Frequent Visitor
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

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.

 

shibabrata27 Frequent Visitor
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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 378 members 3,752 guests
Please welcome our newest community members: