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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JOO13
Resolver I
Resolver I

Cumulative calculation based on Ranking

Dear All, 

 

I'm trying to create a new column with cumulative calculation based on ranking with below expressions but the output is not as per expected. I have tried multiple expressions as well but doesn't fix. I know this expression should be able to work with just a minor changes, anyone knows what is missing here? 

 

Dax expression:

Cummulative of Sales by Customers = VAR CurrentRank = 'Sales'[Rank of Sales] RETURN CALCULATE(SUM('Sales'[Sales]), FILTER (ALL('Sales'[Customers]),'Sales'[Rank of Sales]<=CURRENTRANK))

 

Sample of raw data: (Note: Both "Sales" and "Rank" columns are calculated column)

 

Sales

Customers Sales Rank of Sales 
A200001
B500002
C30003

 

Output from above expression: (The cummulative column basically is just a clone from "Sales" column)

Customers Sales Rank of Sales Cummulative of Sales 
A20000120000
B50000250000
C300033000

 

Desired output: "Cummulative" column should be sum up the total sales based on ranking 

Customers Sales Rank of Sales Cummulative of Sales 
A20000120000
B50000270000
C3000373000

 

Thank you guys! 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@JOO13 

 

Try

 

Cummulative of Sales by Customers =
VAR CurrentRank = 'Sales'[Rank of Sales]
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER ( Sales, 'Sales'[Rank of Sales] <= CURRENTRANK )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@JOO13 

 

Try

 

Cummulative of Sales by Customers =
VAR CurrentRank = 'Sales'[Rank of Sales]
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER ( Sales, 'Sales'[Rank of Sales] <= CURRENTRANK )
    )

Regards
Zubair

Please try my custom visuals

This works like a pro! Thank you, but still couldn't understand why the original expression make a clone from the "Sales" column. 

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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