Cumulative sum Based on Measure

[learner03]

How can I get the desired Cumulative Sum Column?

To be noted that The Location Count column result is calculated Measure and is not directly coming from a column

[danextian]

Hi @learner03 ,

Below are example formula on how to calculate cumulative value. The calculation is based on the alphabetical sorting of order number column.

Cumulative Value - Measure = 
CALCULATE (
    SUM ( Data[Value] ),
    FILTER ( ALL ( Data ), Data[Order Number] <= MAX ( Data[Order Number] )
)
)

Cumulative Value - Column = 
CALCULATE (
    SUM ( Data[Value] ),
    FILTER ( ALL ( Data ), Data[Order Number] <= EARLIER ( Data[Order Number] ) )
)

[learner03]

Hi @danextian The place where you are using -SUm(Data[Value])..I cannot do it because my Location Count Column is a measure and your value column is a column here.

Is there any other work around?

[danextian · Accepted solution]

Hi @learner03,

After hours of research, I hope I have found the solution. Take note that is not my original. It is derived from the proposed solution of  Ashish_Mathur on https://community.powerbi.com/t5/Desktop/Filtering-and-ADDCOLUMNS-field-containing-a-cumulative-sum-... 

LocCount Rank = 
RANKX ( ALL ( Data[Order Number] ), [Location sum],, ASC )
//Rank [Order Number] by Location count 

Cumulative total = 
SUMX (
    TOPN (
        [LocCount Rank],
        CALCULATETABLE ( VALUES ( Data[Order Number] ), ALL ( Data[Order Number] ) ),
        [LocCount Rank], ASC
    ),
    [Sum of Value]
)

sample pbix - https://drive.google.com/file/d/1J_lt38GY3QhY2GJ_numIt7a37j_IBWMl/view?usp=sharing