Forum Discussion

Power_BI_new's avatar
Power_BI_new
Frequent Visitor
3 years ago

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 ro...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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.

Power_BI_new's avatar
Power_BI_new
Frequent Visitor
3 years ago

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