Forum Discussion
Cumulative column Dax
Hello, I want to create a cumulative column. I tried several formulas but I'm stuck for a while.Thank you in advance for your help !
[For information, this is a table of more than 1 million rows
Category = Computed column
Amount no cumulated = Computed column
Step = Computed column (this is a V search which contains steps A,B,C,D but also empty)]
The values for the opening do not change and it must be accumulated in this order:
Opening, V_Calculation,V_Asset,V_Category,B_Calculation
Opening
V_Calculation = Opening + V_Calculation
V_Asset =Opening + V_Calculation+V_Asset
V_Category = Opening + V_Calculation+V_Asset+V_Category
B_Calculation = Opening +B_Calculation
Here's a little preview :
| Category | Step. | Amount no cumulated | Desired result |
| B_Calculation | A | 5 | 13+5=18 |
| B_Calculation | B | 10 | 70+10=80 |
| B_Calculation | C | -20 | 10+(-20)=-10 |
| B_Calculation | D | -51 | .... |
| Opening | C | 10 | 10 |
| Opening | D | 34 | 34 |
| Opening | A | 13 | 13 |
| Opening | B | 70 | 70 |
| V_Calculation | C | -23 | =10+(-23)=-13 |
| V_Calculation | A | -20 | =13+(-20)=-7 |
| V_Calculation | B | -97 | =70+(-97)=-27 |
| V_Category | C | -33 | .... |
| V_Category | A | 21 | .... |
| V_Asset | C | 64 | .... |
| V_Asset | A | -93 | .... |
| V_Asset | B | -69 | .... |
5 Replies
- lbendlin
Super User
This doesn't look like a cumulative function. Each of the step values are handled independently?
It's in this order :
V_Calculation = Opening + V_Calculation
V_Asset =Opening + V_Calculation+V_AssetV_Category = Opening + V_Calculation+V_Asset+V_Category
B_Calculation = Opening +B_Calculation
it's just for B_calculation that it's different- lbendlin
Still not clear to me. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
v-jingzhangCommunity Support
Hi Power_BI_new
If I understand it correctly, you can first add a calculated column with below DAX
Index = SWITCH([Category], "Opening",1, "B_Calculation",2, "V_Calculation",3, "V_Asset",4, "V_Category",5)Then create the second calculated column
Result = SWITCH([Index], 1,[Amount no cumulated], 2,SUMX(FILTER('Table','Table'[Step.]=EARLIER('Table'[Step.])&&'Table'[Index]<=2&&'Table'[Index]>=1),'Table'[Amount no cumulated]), SUMX(FILTER('Table','Table'[Step.]=EARLIER('Table'[Step.])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>=1&&'Table'[Index]<>2),'Table'[Amount no cumulated]) )This could give you the desired result. This calculation may take some time to complete as this table has more than 1 million rows.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hello thank you very much for your answer, but I don't get the desired result and I don't understand why.
For the Opening category, the numbers don't move, which is ok. But if we go to the next step: V_Calculation , for example for step B, it gives me the sum of when I filter in my table on these elements, it does not give me the sum of the opening + the digits of V_Calculation
