Forum Discussion

mauko's avatar
mauko
Regular Visitor
5 years ago
Solved

Cumulative sum even accross filtered data

We would like to analyze employee overtime with cumulative sum which would take into account all data even if they are filtered out by a slicer.  What I mean is that if an employee registers worktime from January 1st onwards, and by the end of March the cumulative overtime is x hours, when looking at the data for April the x would be taken into account as the "seed" number for April.  Now when I filter the worktime to only show April, the cumulative number starts from zero.  I tried to do this with a measure, but not lucky yet.  Is this possible?

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi mauko 

     

    You need to use a measure and use CALCULATE and ALL to get the final result. something similar to:

    Total Hours = CALCULATE (SUM(Table[Hours]), FILTER (ALL(Table[Date]),Table[Date] <= MAX (Table[Date]) )

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi mauko 

     

    You need to use a measure and use CALCULATE and ALL to get the final result. something similar to:

    Total Hours = CALCULATE (SUM(Table[Hours]), FILTER (ALL(Table[Date]),Table[Date] <= MAX (Table[Date]) )
    • mauko's avatar
      mauko
      Regular Visitor
      5 years ago

      Obrigado Miguel,

      That worked.  I don't know what I was doing wrong before as I do think I already tried something like that.  One thing I find very difficult with PowerBI is to debug the measures...

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi mauko ,

         

        The debugging of measures can imply to have concatanex, checking the filtering of tables,ca n be done in several different ways but one approach that I always try to follow is to filter out the information using the external tools liem DAXSTUDIO and TABULAREDITOR.