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.

The problem is that in the original table the comparison field is always the same (QTY = 1), but the summarization depends on the applied filters.

 

Table

 

 Example

Expected values

NC_CODEQTYRunning Total MEASUREExpected Values
SCANNERTYPE1351187135
CALBUTTON1291187264
LIN15KG991187363
LCTYPE961187459
SENTRYFNL771187536
TESTPORT771187613
CALIBRATION571187670
ERASEEMMC461187716
POWERUP461187762
LIN2_5KGD431187805
ECCENTRICITY_RR411187846
READLABEL381187884
LIN1KG361187920
LOADAPP321187952
LOADWAVES251187977
INSTRUCTIONS2411871001
LIN2_5KG1911871020
LOADTOPMN1811871038
LOADCFG1611871054
SENTRYRNL1411871068
TPORTOFF1311871081
CURRENT1111871092
ECCENTRICITY_RF1111871103
SCALE1011871113
SETHWID911871122
EXERCISE611871128
LIN1KGD611871134
SELFTESTMAN611871140
SETIF611871146
LIN10KG511871151
LIN6KG511871156
MAXKG411871160
ENGAGE311871163
LCCHKSUM311871166
LIN15KGD311871169
SENTRYRFL311871172
SOME RANDOM311871175
ZEROACCURACY311871178
ECCENTRICITY_LR211871180
LIN10KGD211871182
COUNTMODE0111871183
ECCENTRICITY_LF111871184
LCSPSWID111871185
LIN6KGD111871186
SET_IF111871187

 

Here you can find the link to the file

Complex Cumulative.pbix 

 

Any help would be greatly appreciated.

 

Regards

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.

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper 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.

  • 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.