Forum Discussion
Solved
Cumulative sum on a ranked table
I'm trying to extract the top % of products by sales for each customer. the script below returns the cumulative percentage but not in order. What I want is to sort them from high to low and then run ...
output
measures :
ranking measure :rnk = RANKX( ALLSELECTED(tbl1[Product]),CALCULATE(sum(tbl1[monthly sales])),,DESC)measure cumul :
cumulative sum = var current_ranking = [rnk] var s = CALCULATE( SUM(tbl1[monthly sales]), FILTER( ALLSELECTED(tbl1[Product]), [rnk] <=current_ranking ) ) return scumult%
cumult % = [cumulative sum] / CALCULATE(SUM(tbl1[monthly sales]), ALLSELECTED(tbl1[Product]) )if my ansswer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
| Customer | Product | monthly sales | Sales% | Comulative% |
| CustA | Prod1 | 236 | 22% | 22% |
| CustA | Prod2 | 185 | 17% | 39% |
| CustA | Prod3 | 181 | 17% | 55% |
| CustA | Prod4 | 142 | 13% | 68% |
| CustA | Prod5 | 136 | 12% | 81% |
| CustA | Prod6 | 115 | 11% | 91% |
| CustA | Prod7 | 95 | 9% | 100% |
| CustB | Prod1 | 92 | 19% | 19% |
| CustB | Prod2 | 86 | 18% | 36% |
| CustB | Prod3 | 79 | 16% | 53% |
| CustB | Prod4 | 79 | 16% | 69% |
| CustB | Prod5 | 78 | 16% | 85% |
| CustB | Prod6 | 75 | 15% | 100% |
I cant share teh data model. the data is sensitive. But this is more or less what i have. I want to calculate the running total for all product for each customer sorted from high to low
output
measures :
ranking measure :
rnk =
RANKX(
ALLSELECTED(tbl1[Product]),CALCULATE(sum(tbl1[monthly sales])),,DESC)
measure cumul :
cumulative sum =
var current_ranking = [rnk]
var s =
CALCULATE(
SUM(tbl1[monthly sales]),
FILTER(
ALLSELECTED(tbl1[Product]),
[rnk] <=current_ranking
)
)
return s
cumult%
cumult % =
[cumulative sum]
/
CALCULATE(SUM(tbl1[monthly sales]), ALLSELECTED(tbl1[Product])
)
if my ansswer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠