cancel
Showing results for
Did you mean:
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

3 ACCEPTED SOLUTIONS
Community Champion

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

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

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

Anonymous
Not applicable

Hi,

Below is the solution screenshot for your requirement.

Regards,

Pavan Vanguri.

6 REPLIES 6
Anonymous
Not applicable

Hi,

Below is the solution screenshot for your requirement.

Regards,

Pavan Vanguri.

Frequent Visitor

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 ?

Community Champion

can you share the layout of underlying data table?

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

Hi I have data format something like this.

User     Day    Amount

U1        Day0    1000

U2       Day2      3000

U2        Day1     234

U3        Day0     345

Community Champion

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

Thank you for the kudos 🙂

Proud to be a Super User!

Frequent Visitor

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors