Forum Discussion
Cumulative_Total based on QM-lot
Hi All,
I'm stcuk with something. I have a table which contains Mrp_Elements, QI, Quantities.
So, here I have a column that has a QI column which is same for all the MRP_elements and then there is a Quantities column that changes for every MRP_element. Now I want to achieve a column i.e. Cumulative_Total in such a way that it takes the cumulative only for QM-Lot(MRP_Element).
| MRP_Elements | QI | Quantities |
| STO | 200 | 50 |
| STO | 200 | 25 |
| QM-lot | 200 | 125 |
| STO | 200 | 50 |
| QM-lot | 200 | 75 |
| STO | 200 | 50 |
| QM-lot | 200 | 20 |
I'll elaborate the query for better understanding. Now the other column that I want to achieve will be in the following way:
When the MRP_element is STO or anything else the new column will give the QI as the result i.e. for MRP_element: STO, QI =200, Quantities = 50, Cum_total = 200. Where as if the Mrp_element = QM-lot,QI=200, Quantities = 125, Cum_total = 200-125=75 and the achieved cum_total should appear the same until the next QM-lot. for the next QM-lot the total would be 75-75 = 0, and so on.
I have attached the table for better understanding.
| MRP_Elements | QI | Quantities | Cum_Total |
| STO | 200 | 50 | 200 |
| STO | 200 | 25 | 200 |
| QM-lot | 200 | 125 | 75 |
| STO | 200 | 50 | 75 |
| QM-lot | 200 | 75 | 0 |
| STO | 200 | 50 | 0 |
| QM-lot | 200 | 20 | -20 |
Would be really helpful if anyone helps me out with this. I'm badly stuck at this.
I don't mind whether it is a measure or a calculated column.
Hope you understood what I tried to explain.
Thanks in advance.
amitchandak Ashish_Mathur Fowmy Jihwan_Kim SpartaBI tamerj1 ChandeepChhabra Vahid-DM
Hi Anonymous
I have added an index column using power query. Here is the smaple file with the solution https://we.tl/t-tfARCM9jCsApologies for not including screenshots as I already consumed my limit of uploaded images but I can assure you it is working fine. The solution is based on a calculated column. Please let me know if you need a measure instead.
Cum_Total = VAR CurrentQI = Data[QI] VAR CurrentIndex = Data[Index] VAR PreviousQMlots = FILTER ( Data, Data[MRP_Elements] = "QM-lot" && Data[Index] <= CurrentIndex ) VAR PreviousQMlotValues = SUMX ( PreviousQMlots, Data[Quantities] ) RETURN CurrentQI - PreviousQMlotValues**Update
Here is the code of the measure https://we.tl/t-OpH3iQQFZC
Cum_Total Measure = VAR CurrentQI = SUM ( Data[QI] ) VAR CurrentIndex = SELECTEDVALUE ( Data[Index] ) VAR PreviousQMlots = FILTER ( ALL ( Data ), Data[MRP_Elements] = "QM-lot" && Data[Index] <= CurrentIndex ) VAR PreviousQMlotValues = SUMX ( PreviousQMlots, Data[Quantities] ) RETURN CurrentQI - PreviousQMlotValues
4 Replies
- tamerj1
Community Champion
Hi Anonymous
I have added an index column using power query. Here is the smaple file with the solution https://we.tl/t-tfARCM9jCsApologies for not including screenshots as I already consumed my limit of uploaded images but I can assure you it is working fine. The solution is based on a calculated column. Please let me know if you need a measure instead.
Cum_Total = VAR CurrentQI = Data[QI] VAR CurrentIndex = Data[Index] VAR PreviousQMlots = FILTER ( Data, Data[MRP_Elements] = "QM-lot" && Data[Index] <= CurrentIndex ) VAR PreviousQMlotValues = SUMX ( PreviousQMlots, Data[Quantities] ) RETURN CurrentQI - PreviousQMlotValues**Update
Here is the code of the measure https://we.tl/t-OpH3iQQFZC
Cum_Total Measure = VAR CurrentQI = SUM ( Data[QI] ) VAR CurrentIndex = SELECTEDVALUE ( Data[Index] ) VAR PreviousQMlots = FILTER ( ALL ( Data ), Data[MRP_Elements] = "QM-lot" && Data[Index] <= CurrentIndex ) VAR PreviousQMlotValues = SUMX ( PreviousQMlots, Data[Quantities] ) RETURN CurrentQI - PreviousQMlotValues - tamerj1
Hi Anonymous
Do you have an index, date or any unique numeric column?- tamerj1
Anonymous
If it is unique just replace the index column in the code with the date column.