To perform cumulative addition of a column in power bi

[sajal161292]

Hi,

I have prepared the sql query of a report in sql server.

There exists a one to many relationship.

A name can have multiple item_segments against it,an item_segment can have multiple due_dates and a due date should have 6 different order types against it.The requirement is to do cumulative addition of the quantity in all the weeks for a particular name and item_segment.

So I would like to have a column cumulativeweekly that could do the cumulative addition of the current week's total with the succeeding weeks.

For Week 50 = Total of week 49 + Total of week 50 = -7302 + (-2834) etc.

Please help me in achieving this.

[v-yuezhe-msft]

@sajal161292,

I make a test in the following sample table. Just create a measure using DAX below.

Cummulative = CALCULATE(SUM(Table[quantity]),FILTER(ALLEXCEPT(Table,Table[Name],Table[item_segments]),Table[week_number]<=MAX(Table[week_number])))

If the above DAX don't help, please share dummy data of your table for us to analyze.

Regards,
Lydia

[sajal161292]

Hi,

Thanks for the solution!!

It is working for me but it is giving the same value for me against every order type.

I would like it to have against the order type that are having quantity values in it.

As an example,the order type is having the value -4500 should have cummulative field with value -4500 and the other order types should have 0 against them.

[v-yuezhe-msft]

@sajal161292，

Could you please export data of your original table to Excel and share me the Excel file via Private Message?

Regards,
Lydia

[sajal161292]

Hi,

By mistake i published it in the accepted solution.

I am unable to attach my excel file via private message.

I can share it with you through google drive if that is covenient for you

[v-yuezhe-msft]

@sajal161292,

Please post shared link of  the file.

Regards,
Lydia

[sajal161292]

Hi,

Please find the link of the file:

https://drive.google.com/drive/folders/1iYZulMyMkohvrNyzGDM_pGQHN9TgacHX?usp=sharing

[v-yuezhe-msft]

@sajal161292,

Please create the following measure and check if you get expected result.

Cummulative = CALCULATE(SUM(Sheet5[quantity]),FILTER(ALLEXCEPT(Sheet5,Sheet5[Name],Sheet5[item_segments],Sheet5[order_type_text]),Sheet5[week_number]<=MAX(Sheet5[week_number])))

Regards,
Lydia

[sajal161292]

Hi,

Thanks for your solution!!

But it is not performing the calculation correctly.

Thanks

[v-yuezhe-msft]

@sajal161292，

Please post expected result in table format based on the sample data you shared to me.

Regards,
Lydia

[sajal161292]

Hi,

Please find the excel file in the location with the result.

I need to represent it in matrix layout:

https://drive.google.com/open?id=1iYZulMyMkohvrNyzGDM_pGQHN9TgacHX

[v-yuezhe-msft]

@sajal161292,

Create the following measure in your table.

Cumulative3 = IF(MAX(Sheet5[quantity])<>0,
		   CALCULATE(SUM(Sheet5[quantity]),
	                     FILTER(ALL(Sheet5),
				    Sheet5[due_date]  <= MAX(Sheet5[due_date]))), 
		   0    
)

Regards,
Lydia

[sajal161292]

Hi,

I used this in my visual in Power BI but it is not giving me correct results.

[v-yuezhe-msft]

@sajal161292,

Please review my screenshot. Do you get expected data when importing the sample file which you shared to me into Power BI Desktop and create the measure? Please ensure that you right click your table and select "New Measure" , then apply the DAX formula.


Regards,
Lydia

[sajal161292]

Hi,

Apologies for the inconvenience!!

I would like to represent in the format as illustrated in the screenshot below.

I want to calculate the quantity total for each item-segment with different order types foe each week and then pass this value to the next week's pqr order type and similarly doing this operation for the consecutive weeks.

Please help me in doing this.

[sajal161292]

Hi,

Apologies for the inconvenience!!

I would like to see it in this format in Power BI now.

I want to represent each item-segment and then order types in this format.

Then i will need to take the cumulative total of each week and then pass this total to the pqr order type of next week and similarly for the consecutive weeks  as shown above.

Please help me in performing this operation.

[v-yuezhe-msft]

@sajal161292,

It is not possible to create the Matrix visual as you describe in Power BI Desktop. You need to add the measure to Matrix visual to represent total of weeks. For more details, please review the following PBIX file.

https://1drv.ms/u/s!AhsotbnGu1NogxF4bHXGWVWMQoBn

Regards,
Lydia

[sajal161292]

Hi,

Is there any other alternative way to do that through matrix visual..like if I create stored procedures for doing this operation in order to store the values for different weeks then will be it be possible to achieve in Power BI?

My manager wants it urgently and wants it in this particular format only.

Also is there any other tool that can help me in achieving this objective?

[sajal161292]

Hi,

I have somehow obtained a field as given in the solution below:

I have created a calculated measure in my visual as:

updated_qty = CALCULATE(ShortageData[Cummulative],FILTER(ALLEXCEPT(ShortageData,ShortageData[Name],ShortageData[item_segments]),ShortageData[week_number] +1<=MAX(ShortageData[week_number])))

Now in week 49 i need to have the value of the field for one order type as -1500(it is 2066) currently and 0 for the other order type (which is also 2066) currently.

Basically i would need it to replace with the current week's quantity value and the 3rd one should have the rolling quantity total of previous week(which is 2066).

It should perform the same operation in the consecutive weeks.

I want to use switch statement  which is not helping me in this case.

Can someone please help me in doing it?

[sajal161292]

Hi,

Any pointers are highly appreciated!!

Can someone please help me in performing this task?