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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
marry
Helper I
Helper I

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

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

marry_0-1700558905802.png

 

 

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

4 REPLIES 4
lbendlin
Super User
Super User

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?

hi @lbendlin ,

 

Yes, it will be acceptable for me.

 

Might be easier to revert the sign for Demand.

 

lbendlin_1-1700864870535.png

 

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!! 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.