- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Calculate the difference between two columns in matrix

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 10:00 PM

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 !

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 07:50 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 10:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 11:17 PM

Hi,

Below is the solution screenshot for your requirement.

Click Here to download the sample application.

Hope it help you.

Regards,

Pavan Vanguri.

All Replies

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 03:41 AM

can you share the layout of underlying data table?

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 03:59 AM

Hi I have data format something like this.

User Day Amount

U1 Day0 1000

U2 Day2 3000

U2 Day1 234

U3 Day0 345

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 07:50 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 10:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 11:17 PM

Hi,

Below is the solution screenshot for your requirement.

Click Here to download the sample application.

Hope it help you.

Regards,

Pavan Vanguri.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019 03:27 AM

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 ?