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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |