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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

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.

Top Solution Authors