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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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