Forum Discussion
Cumulative SUM by CODE
Hi Everyone,
How can I create a "Coste Actual Estimado" column that shows an accumulated value, something like this:
| Coste Mensual Estimado | Coste Total 2015 | Coste Actual Estimado | |
| Month | 0 | 50 | 50 |
| January | 0 | 50 | 50 |
| February | 0 | 50 | 50 |
| March | 0 | 50 | 50 |
| April | 0 | 50 | 50 |
| May | 0 | 50 | 50 |
| June | 0 | 50 | 50 |
| July | 15 | 50 | 65 |
| August | 10 | 50 | 75 |
| September | 15 | 50 | 90 |
| October | 0 | 50 | 90 |
| November | 35 | 50 | 125 |
| December | 40 | 50 | 165 |
In addition, I have to disinguish the values by CODE (cause I have a lot of projects).
Thanks
Hi as3znaus,
Based on the sample data above, you can create "Coste Actual Estimado" column using below formula. But, you should add an index column in Query Editor.
Coste Actual Estimado = CALCULATE ( SUM ( 'Cumulative SUM'[Coste Mensual Estimado] ), FILTER ( 'Cumulative SUM', 'Cumulative SUM'[Index] <= EARLIER ( 'Cumulative SUM'[Index] ) ) ) + 'Cumulative SUM'[Coste Total 2015]What do you mean "disinguish the values by CODE"? Is "CODE" a column in your source table? For further analysis, would you please share some more sample data and describe more detailed about "CODE"?
Thanks,
Yuliana Gu
4 Replies
Hi as3znaus,
Based on the sample data above, you can create "Coste Actual Estimado" column using below formula. But, you should add an index column in Query Editor.
Coste Actual Estimado = CALCULATE ( SUM ( 'Cumulative SUM'[Coste Mensual Estimado] ), FILTER ( 'Cumulative SUM', 'Cumulative SUM'[Index] <= EARLIER ( 'Cumulative SUM'[Index] ) ) ) + 'Cumulative SUM'[Coste Total 2015]What do you mean "disinguish the values by CODE"? Is "CODE" a column in your source table? For further analysis, would you please share some more sample data and describe more detailed about "CODE"?
Thanks,
Yuliana Gu1. Create one index column for this in Query editor with what sorting u want.
2. Create column
Column = var index_1 = index
var sum_of_Coste_Mensual_Estimado = calculate ( sum(Coste Mensual Estimado), filter (all(Table_Name), index < index_1 )
var Val_Coste_Total_2015 = 'Coste Total 2015'
return Val_Coste_Total_2015 + sum_of_Coste_Mensual_Estimado
Try this , I hope it will help u dude,
let me know if it is not helping u
