Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Power-BI4CPF
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

@Power-BI4CPF 

output

Daniel29195_0-1707550363179.png

 

 

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

 

View solution in original post

4 REPLIES 4
Daniel29195
Super User
Super User

@Power-BI4CPF 

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

CustomerProductmonthly salesSales%Comulative%
CustAProd123622%22%
CustAProd218517%39%
CustAProd318117%55%
CustAProd414213%68%
CustAProd513612%81%
CustAProd611511%91%
CustAProd7959%100%
CustBProd19219%19%
CustBProd28618%36%
CustBProd37916%53%
CustBProd47916%69%
CustBProd57816%85%
CustBProd67515%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

@Power-BI4CPF 

output

Daniel29195_0-1707550363179.png

 

 

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

 

PowerBI4CPF_1-1707515259573.png

 

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%)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.