cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge. Frequent Visitor

## 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 5  Community Support

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. Frequent Visitor

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  Super User

This doesn't look like a cumulative function.  Each of the step values are handled independently? Frequent Visitor

It's in this order :

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

it's just for B_calculation that it's different  Super User

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-...
Please show the expected outcome based on the sample data you provided. Announcements #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (1,701)