Forum Discussion
cummulative sum
hi
how to create below calculation in Power in a new column.
| Orderd qty =SUMIF($A$2:A2,A2,$B$2:B47) |
SKU is column A , BACK QTY is column B and ordered qty is the result qty as per above formula in excel.
| SKU # | BACK QTY | Ordered qty |
| IX110100670 | 1000 | 1000 |
| IX110100779 | 1000 | 1000 |
| IX110100670 | 1000 | 2000 |
| IX110100779 | 1000 | 2000 |
| IX110100670 | 1000 | 3000 |
| IX110100670 | 1000 | 4000 |
| IX110100670 | 1000 | 5000 |
| IX110100670 | 1000 | 6000 |
| IX110100670 | 3000 | 9000 |
| IX110100779 | 20000 | 22000 |
Hi sunil3M ,
Try the following formual:
Order qty1 = SUMX ( FILTER ( 'Table', [SKU #] = EARLIER ( [SKU #] ) && [Index] <= EARLIER ( [Index] ) ), [BACK QTY] )
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
sunil3M , You need have date column. If not add an index column and try
Add an index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
New column = sumx(filter(Table, [SKU #] =earlier([SKU #]) && [Index] <= earlier([Index])),[BACK QTY])
or new measure =
sumx(filter(allselected(Table), Table[SKU #] =max(Table[SKU #]) && Table[Index] <= max(Table[Index])),Table[BACK QTY])Hi sunil3M ,
Try the following formual:
Order qty1 = SUMX ( FILTER ( 'Table', [SKU #] = EARLIER ( [SKU #] ) && [Index] <= EARLIER ( [Index] ) ), [BACK QTY] )
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
