cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JOO13
Helper I
Helper 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


View solution in original post

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_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors