Forum Discussion
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
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
3 Replies
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 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
