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.

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Gpensabene ,

I have tested the pbix file that MFelix did. It works perfectly. If it works for you, please accept the helpful answer as a solution. If you still have questions, please feel free to ask us.