Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
marry
Helper I
Helper I

Power Query - Cumulated row

Hi everyone,

 

Would like to have the new added rows display in my Power BI Matrix table to calculate the EOH Quantity, the logic is a little bit complicated, please check below detailed requirement. What I need in the end is to implement it in Power BI report.

Hope the experts can help on this, it will save my day. Thank you.

 

Requirement:

Yellow rows are the expected to-be-generated views in my PBI report

 

marry_1-1700750995026.png

 

  • EOH generation need to be calculated Per Product Name + OSAT Supplier (for example, one Product Name with 2 different OSAT suppliers, need to generate 2 EOH rows)
  • EOH for current month= (Current month TK Supply BOH Qty sum + TK Supply Qty sum+ CS Supply BOH Qty sum + CS Supply Qty sum) –Demand
  • EOH for the following month = (Previous month EOH + TK Supply Qty sum + CS Supply Qty sum)-Demand
  • Report sequence sorted by “Identifier” column: Demand>> TK Supply>> CS Supply>> EOH

Here is my PBI file: (It seems I cannot attach the pbi file directly, please let me know if you can open it.)

https://drive.google.com/file/d/1PWoOq2Y2iP45ykuD5CnP5lD_dAIMFrYi/view?usp=sharing

 

Excel sample result calculation:

https://docs.google.com/spreadsheets/d/1MVP8ptlkKTT-AssKP2rlI0MKbPJirhFS/edit?usp=sharing&ouid=10968...

 

Cheers

Marry

2 REPLIES 2
ronrsnfld
Super User
Super User

Your pbi file is not useful as the Source refers to a file in your personal workspace, which is not accessible.

 

 

Hi @ronrsnfld ,

 

I put the excel source file here, 

https://docs.google.com/spreadsheets/d/17ut6MHGqxfiGui5RbmIYJBS4jUvjXlxh/edit?usp=sharing&ouid=10968...

 

My current PBI Matirx  table look like below:

marry_0-1700785275030.png

 

 

My PBI Source data: (Please get the data from above excel which I provided in the google drive)

marry_1-1700785327760.png

 

Cheers,

Marry

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors