Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
Try
Cummulative of Sales by Customers = VAR CurrentRank = 'Sales'[Rank of Sales] RETURN CALCULATE ( SUM ( 'Sales'[Sales] ), FILTER ( Sales, 'Sales'[Rank of Sales] <= CURRENTRANK ) )
Try
Cummulative of Sales by Customers = VAR CurrentRank = 'Sales'[Rank of Sales] RETURN CALCULATE ( SUM ( 'Sales'[Sales] ), FILTER ( Sales, 'Sales'[Rank of Sales] <= CURRENTRANK ) )
This works like a pro! Thank you, but still couldn't understand why the original expression make a clone from the "Sales" column.
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |