Forum Discussion

Rana's avatar
Rana
Regular Visitor
10 years ago
Solved

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 ne...
Need Help
  • Tsanka's avatar
    Tsanka
    10 years ago

     @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]
)

     

     

leonardmurphy's avatar
leonardmurphy
Skilled Sharer
10 years ago

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.

 

 

  • Rana's avatar
    Rana
    Regular Visitor
    10 years ago

    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's avatar
      Rana
      Regular Visitor
      10 years ago

      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's avatar
        leonardmurphy
        Skilled Sharer
        10 years ago

        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)