Forum Discussion

learner03's avatar
learner03
Post Partisan
4 years ago
Solved

Cumulative sum Based on Measure

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    
Need Help
learner03's avatar
learner03
Post Partisan
4 years ago

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's avatar
danextian
Super User
4 years ago

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-total/m-p/344022 

 

 

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