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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors