How to generate new row to calculate the EOH quantity (Supply- Demand) calculation?

[marry]

Hi everyone,

Would like to have the new added rows display in my Matrix table to calculate the EOH Quantity, hope the experts can help on this!!! Thank you.

 Requirement:

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

• EOH generation need to be calculated Per Product Name + OSAT Supplier (for example, one Product Name with 2 different OSAT supliers, 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...

Best regards,

Marry

[lbendlin]

You cannot add rows to a data model dynamically.  You can only do that statically by creating calculated tables.  Is that acceptable in your scenario?

[marry]

hi @lbendlin ,

Yes, it will be acceptable for me.

[lbendlin]

Might be easier to revert the sign for Demand.

[marry]

Thanks @lbendlin .

Good thinking and proposal, I will think about how to implement the rest of logics in my PBI report. Thanks for the help!! 🙂