cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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 the code below.

CumulativePercentage =
VAR _TotalSales =
CALCULATE(
[Sales_kgs],
ALL('Product Lookup')
)
VAR _CumSales =
CALCULATE(
SUM(Inventory[monthly_wholesale_kg]),
FILTER(
ALLSELECTED(Inventory),
Inventory[product_sku] <= MAX(Inventory[product_sku])
)
)

RETURN
_CumSales / _TotalSales
1 ACCEPTED SOLUTION
Super User

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! 🤠

4 REPLIES 4
Super User

please if possible, share some sample data, and display the desired output.

New Member
 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

Super User

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! 🤠

New Member

This is a list of all the product for a certain customer. The sales column is already sorted from high to low. I want the cumlutavie% column to be the running sum of Sale%. right not it is calculating the running sum but in a different order. In the end I want to be able to filter products by the top % (i.e. top50% or top 80%)