Forum Discussion

Gpensabene's avatar
Gpensabene
Frequent Visitor
6 years ago
Solved

Complex Cumulative (Running) Totals on filtered Table

Hi expert, I will try to share the complex scenario with the following example.   The Cumulative sum doesn't work with my scenario, or rather, the calculation is correct but it is not what I want....
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Gpensabene ,

     

    Create a measure for the total quantity and use that measure as your value in the filter so you will need this two measures:

     

    TotalQTY = SUM ('Table'[QTY])



Running Total MEASURE__ =
IF (
    NOT ( ISBLANK ( [TotalQTY] ) );
    CALCULATE (
        [TotalQTY];
        FILTER ( ALL ( 'Table'[NC_CODE] ); SUM ( 'Table'[QTY] ) <= [TotalQTY] )
    )
)

     

    Now if you add to your model you get the result below:

     

     

    Check PBIX file attach.

MFelix's avatar
MFelix
Super User
6 years ago

Hi Gpensabene ,

 

Create a measure for the total quantity and use that measure as your value in the filter so you will need this two measures:

 

TotalQTY = SUM ('Table'[QTY])



Running Total MEASURE__ =
IF (
    NOT ( ISBLANK ( [TotalQTY] ) );
    CALCULATE (
        [TotalQTY];
        FILTER ( ALL ( 'Table'[NC_CODE] ); SUM ( 'Table'[QTY] ) <= [TotalQTY] )
    )
)

 

Now if you add to your model you get the result below:

 

 

Check PBIX file attach.