topic Re: Cumulative Sum for individual material in DAX Commands and Tips

Cumulative Sum for individual material

Neel98 — Wed, 28 Jun 2023 12:08:27 GMT

I want to find out the Cumulative sum for individual materials. I have highlighted the desired result in yellow. Is this possible in DAX?

Re: Cumulative Sum for individual material

some_bih — Thu, 29 Jun 2023 10:15:00 GMT

Hi Neel98 just create measure like Total quantity = SUM(<YOUR TABLE NAME>[Qty]) and put it on table / matrix.

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

Re: Cumulative Sum for individual material

Anonymous — Fri, 30 Jun 2023 07:46:06 GMT

Hi Neel98 ,

You can achieve this in Power BI. You need to add an [Index] or [Date] column to determine the order of the data.

Here I suggest you to add an Index by material group in Power Query Editor.

Sort your table as you want> Group all rows by Material column > Add index column by M query.

Table.AddIndexColumn([Count],"Index",1)

For reference:

Create Row Number for Each Group in Power BI using Power Query

Calculated column:

Cummulative Sum = CALCULATE ( SUM ( 'Table'[Qty] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Material] ), 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) )

Result is as below.

Best Regards,
Rico Zhou

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