cancel
Showing results for
Did you mean:
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 A 20000 1 B 50000 2 C 3000 3

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

 Customers Sales Rank of Sales Cummulative of Sales A 20000 1 20000 B 50000 2 50000 C 3000 3 3000

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

 Customers Sales Rank of Sales Cummulative of Sales A 20000 1 20000 B 50000 2 70000 C 3000 3 73000

Thank you guys!

1 ACCEPTED SOLUTION
Community Champion

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

2 REPLIES 2
Community Champion

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

Helper I

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

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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 Solution Authors
Top Kudoed Authors