Complex Cumulative (Running) Totals on filtered Table

[Gpensabene]

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_CODE	QTY	Running Total MEASURE	Expected Values
SCANNERTYPE	135	1187	135
CALBUTTON	129	1187	264
LIN15KG	99	1187	363
LCTYPE	96	1187	459
SENTRYFNL	77	1187	536
TESTPORT	77	1187	613
CALIBRATION	57	1187	670
ERASEEMMC	46	1187	716
POWERUP	46	1187	762
LIN2_5KGD	43	1187	805
ECCENTRICITY_RR	41	1187	846
READLABEL	38	1187	884
LIN1KG	36	1187	920
LOADAPP	32	1187	952
LOADWAVES	25	1187	977
INSTRUCTIONS	24	1187	1001
LIN2_5KG	19	1187	1020
LOADTOPMN	18	1187	1038
LOADCFG	16	1187	1054
SENTRYRNL	14	1187	1068
TPORTOFF	13	1187	1081
CURRENT	11	1187	1092
ECCENTRICITY_RF	11	1187	1103
SCALE	10	1187	1113
SETHWID	9	1187	1122
EXERCISE	6	1187	1128
LIN1KGD	6	1187	1134
SELFTESTMAN	6	1187	1140
SETIF	6	1187	1146
LIN10KG	5	1187	1151
LIN6KG	5	1187	1156
MAXKG	4	1187	1160
ENGAGE	3	1187	1163
LCCHKSUM	3	1187	1166
LIN15KGD	3	1187	1169
SENTRYRFL	3	1187	1172
SOME RANDOM	3	1187	1175
ZEROACCURACY	3	1187	1178
ECCENTRICITY_LR	2	1187	1180
LIN10KGD	2	1187	1182
COUNTMODE0	1	1187	1183
ECCENTRICITY_LF	1	1187	1184
LCSPSWID	1	1187	1185
LIN6KGD	1	1187	1186
SET_IF	1	1187	1187

Here you can find the link to the file

Complex Cumulative.pbix 

Any help would be greatly appreciated.

Regards

[v-xuding-msft]

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.

[MFelix · Accepted solution]

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.