Forum Discussion
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
- 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:
Cheers
Marry
2 Replies
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,
My current PBI Matirx table look like below:
My PBI Source data: (Please get the data from above excel which I provided in the google drive)
Cheers,
Marry
