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 Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors