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

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)

Rana's avatar
Rana
Regular Visitor
10 years ago

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

    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

    • Betissa's avatar
      Betissa
      Helper I
      9 years ago

      Hello Rana,How did you calculate this field ?

       

       

  • Tsanka's avatar
    Tsanka
    Kudo Collector
    10 years ago

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

      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's avatar
        Tsanka
        Kudo Collector
        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]
)