Forum Discussion

Jtep's avatar
Jtep
Regular Visitor
9 years ago

Cummulative Total with filter retained

Hello, I trying to get a cumulative total working for profit and loss, very similar to that found at http://www.daxpatterns.com/cumulative-total/ My formula: = CALCULATE ( [Total Net Profit and ...
Need Help
GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi Jtep

 

What happens if you remove the ALL filter?

 

My formula:
=
CALCULATE (
[Total Net Profit and Loss],
FILTER (TradeLog_Table ,
TradeLog_Table[Close Date] <= MAX ( TradeLog_Table[Close Date] )
)
)
  • Jtep's avatar
    Jtep
    Regular Visitor
    9 years ago

    Thanks for quick reply!  With ALL removed as per your code, unwanted rows are removed (good), but the profit and loss does not aggregate, ie is not cummulative.   What is returned is the same as my '[Total Net Profit and Loss]' measure, a simple measure of SUM(TradeLog_table[Profit and Loss]).  The aggregation is only working on one row, with the date expression appearing to have no effect.

    • GilbertQ's avatar
      GilbertQ
      Super User
      9 years ago

      Hi there


      Do you perhaps have a diagram of how the filtering is working. It appears as though the filtering is happening with the "Cross Filter Direction" set to both for another table. Which can always complicate things?

      • Jtep's avatar
        Jtep
        Regular Visitor
        9 years ago

        Diagram attached.  A normal one to many relationship established.  Filtering is established by a slicer connected to my lookup table (table at the top).  This selects a particular broker, then the rows on the connected table below are filtered to show only rows with the selected broker(s).  Essentially I want a function like ALL that returns all 'currently filtered rows' opposed to 'all' rows'.