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

Difference between 2 columns

I have a Matrix in PowerBI which is showing data for 2 months (month is a string not a date column like Aug, Sept or any selected 2 months). Matrix shows me Total for both months, instead of Sum i need the difference (reduction or addition) between 2 months. Is it doable?

1 ACCEPTED SOLUTION

Accepted Solutions
Tsanka Member
Member

Re: Difference between 2 columns

 @Rana,

 

A much cleaner code than the one posted by me before (and deleted) is the following:

 

PreviousDay = CALCULATE
(
  SUM (FactData[Employees]);
  Filter (ALL(FactData[Date]);FactData[Date]=Max(FactData[Date])-1)
)

Difference = IF(
   ISBLANK([PreviousDay]);
   0;
   SUM(FactData[Employees])-[PreviousDay]
)

 

 

View solution in original post

10 REPLIES 10
leonardmurphy Established Member
Established Member

Re: Difference between 2 columns

No, a Matrix couldn't work with a subtraction formula in place of the totals. 

 

How would the matrix know whether you want to subtract Aug from Sep vs. Sep from Aug (given the months are strings)? What would you want to happen if a person picks 3 or more months instead of just 2? 

 

A measure would probably be your way forward. You could create a measure that subtracts a previous month value for any given month. But it wouldn't work dynamically with any two random months.

 

 

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
Highlighted
Rana Frequent Visitor
Frequent Visitor

Re: Difference between 2 columns

Everything you said I already have in my mind, you can do this simply in Excel but not in Power BI, stumped a bit.

Rana Frequent Visitor
Frequent Visitor

Re: Difference between 2 columns

I actually was able to do that in Tableau, it has an option to get a differencial between values in a Matrix. I just had to hide the 1st selected Month.

leonardmurphy Established Member
Established Member

Re: Difference between 2 columns

I don't know how that feature works in Tableau...but I can see why you'd want to do such a thing. 

 

Even in Excel though, you'd have to do the calculation outside of a single pivot table, wouldn't you?

 

Perhaps an opportunity for a custom visualization (one that allows you to select & then does a side-by-side comparison of 2 selected dates/categories/other attributes)

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
Rana Frequent Visitor
Frequent Visitor

Re: Difference between 2 columns

Yes in Excel it we created 3 pivot tables, 2 for each months and 3rd for differential. Can we do something similar in Power BI?

Rana Frequent Visitor
Frequent Visitor

Re: Difference between 2 columns

It is sort of working in Power BI but for 1st month in the list it shows the same number. Screen shot below:

 

Difference.JPG

Tsanka Member
Member

Re: Difference between 2 columns

It seems that you are interested in data for 2 specific months (perhaps current month and the previous month). I would solve your task by adding 3 new measures - one to sum the values for the current month, the second - for the previous month and the 3rd - for the difference. Those measures can be visualized either in a table or in a matrix (with no fields placed in the column area)

 

PS: While me writing the above comment you have posted the screenshot with your matrix. I don't quite understand your example as requirements and structrure. Therefore please ignore my idea for a solution posted before

Rana Frequent Visitor
Frequent Visitor

Re: Difference between 2 columns

Tsanka,

 

1st column for a particular Date shows total number of Employees, 2nd column is the difference. So in the case of 1st month in the table it shows the same number as it doenst have anything to compare with (should be 0). All other months shows the difference between the month with the previous month.

Tsanka Member
Member

Re: Difference between 2 columns

 @Rana,

 

A much cleaner code than the one posted by me before (and deleted) is the following:

 

PreviousDay = CALCULATE
(
  SUM (FactData[Employees]);
  Filter (ALL(FactData[Date]);FactData[Date]=Max(FactData[Date])-1)
)

Difference = IF(
   ISBLANK([PreviousDay]);
   0;
   SUM(FactData[Employees])-[PreviousDay]
)

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors