Skip to main content
cancel
Showing results for 
Search instead 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

Reply
TRIXTERBINOOB
Regular Visitor

Cumulative Sum BY CLIENT and without date or index column

Hello, dear comunity:

I'm here cause need help with doing a cumulative sum of sales wich I calculated it with SUM(), need to be representated on a pivot table in excel or matrix in Power BI with DAX. If I do a sum of sales and bring customername to labels I have this result, and I need a solution to receive the orange column's behavior. the sales must be ordered by "Total sales in period" value.

TRIXTERBINOOB_0-1714775740294.png

 

I got the sample data in this url to practice: Sample Sales Data (kaggle.com)

 

Sorry for my bad grammar in english, I'm still learning.

 

Kind regards.

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@TRIXTERBINOOB 

Measure = 
CALCULATE (
    [Sales],
    WINDOW ( 
        1, ABS, 0, REL, 
        ALL ( 'Table'[Etiquetas de fila] ),
        ORDERBY ( [Sales], DESC )
    )
)

or

Measure 2 = 
VAR CustomerSales = 
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Etiquetas de fila] ),
        "@Sales", [Sales] 
    )
VAR Result = 
    SUMX (
        WINDOW ( 
            1, ABS, 0, REL, 
            CustomerSales,
            ORDERBY ( [@Sales], DESC )
        ),
        [@Sales]
    )
RETURN 
    Result

 

View solution in original post

3 REPLIES 3
AntrikshSharma
Community Champion
Community Champion

@TRIXTERBINOOB 

Measure = 
CALCULATE (
    [Sales],
    WINDOW ( 
        1, ABS, 0, REL, 
        ALL ( 'Table'[Etiquetas de fila] ),
        ORDERBY ( [Sales], DESC )
    )
)

or

Measure 2 = 
VAR CustomerSales = 
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Etiquetas de fila] ),
        "@Sales", [Sales] 
    )
VAR Result = 
    SUMX (
        WINDOW ( 
            1, ABS, 0, REL, 
            CustomerSales,
            ORDERBY ( [@Sales], DESC )
        ),
        [@Sales]
    )
RETURN 
    Result

 

It actually worked!

 

I didn't knew about WINDOW function I investigated documentation and learned a lot from you.

 

Thank u very much!

DataNinja777
Super User
Super User

Hi @TRIXTERBINOOB ,

 

You can achive your required output in many different ways and the following is an example.  

First create a ranking of sales by customers.  

DataNinja777_0-1714802402771.png

Then, create a cumulative total in the order of customer sales ranking by value.  

DataNinja777_1-1714802521592.png

I attach an example pbix file below:

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.