topic Cumulative sum based on other columns in DAX Commands and Tips

Cumulative sum based on other columns

georgec96 — Tue, 17 Jan 2023 16:06:28 GMT

Hi everyone,

I've been struggling to write a calculated column to solve the following issue.

I've got 4 columns: SKU,PO,Qty ordered and Qty on backorder. I need to create a column called "can_clear_backorders", that will calculate the amount of backorders a PO can clear based on the qty ordered but this needs to be a cumulative sum and it also needs to reset when there is a new value in column SKU.

Any ideas how can I achive this? Sorry if my explanation is not clear enough, I've attached an image with the expected result as an example.

Re: Cumulative sum based on other columns

amitchandak — Wed, 18 Jan 2023 02:13:05 GMT

georgec96 , You back order logic is not clear.

But windows function with partitioned by can help

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://www.youtube.com/watch?v=cN8AO3_vmlY&t=31980s

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Re: Cumulative sum based on other columns

v-zhangti — Thu, 19 Jan 2023 07:53:28 GMT

Hi, georgec96

You can try the following methods. You need to add an index column to Power Query first.

Column:

Max Oty on backorder = MAXX(FILTER('Table',[SKU]=EARLIER('Table'[SKU])),[Qty on backorder])

Cumulative = CALCULATE(SUM('Table'[Qty ordered]),FILTER('Table',[SKU]=EARLIER('Table'[SKU])&&[Index]<EARLIER('Table'[Index])))

Result = SWITCH(TRUE(), [Cumulative] = BLANK ()&& [Qty ordered] <= [Qty on backorder], [Qty ordered], [Cumulative] = BLANK ()&& [Qty ordered] > [Qty on backorder],[Qty on backorder], [Max Oty on backorder]-[Cumulative]<0,0, [Qty ordered]<[Cumulative],[Qty ordered],[Max Oty on backorder]-[Cumulative])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.