Forum Discussion
Cumulative sum Based on Measure
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 countCumulative 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
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] ) )
)
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?
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 countCumulative 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
