Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Power_BI_new
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_Calculation513+5=18
B_CalculationB1070+10=80
B_CalculationC-2010+(-20)=-10
B_CalculationD-51....
OpeningC1010
Opening D3434
Opening A1313
Opening B7070
V_CalculationC-23=10+(-23)=-13
V_CalculationA

-20

=13+(-20)=-7
V_CalculationB-97=70+(-97)=-27
V_CategoryC-33....
V_CategoryA21....
V_AssetC64....
V_AssetA-93....
V_AssetB-69....

 

5 REPLIES 5
v-jingzhang
Community Support
Community 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])
)

vjingzhang_0-1683191998593.png

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

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

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.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors