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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

UID Stock

Hi Team,

 

I need "Remaining Stock" column result basd on UID (Uinque) sum  for "[UID in Stock] -[Prod_Total]".
For example : UID 30.A9KJ  [UID in Stock]-[Prod_Total] and result shows all UID 30.A9KJ rows

 

Formulas are,

1) 

UID Stock = DIVIDE(_x,_y)

_x=
Var sum_unrest = CALCULATE(SUM('UID_Stock'[Unrest.stock of UID]),FILTER('UID_Stock','UID_Stock'[Type] = "Stock"))
var sum_qty = CALCULATE(SUM('UID_Stock'[Qual.insp.stock of UID]),FILTER('UID_Stock','UID_Stock'[Type] = "Stock"))
var avg_unrest = CALCULATE(AVERAGE('UID_Stock'[Unrest.stock of UID]),FILTER('UID_Stock','UID_Stock'[Type] = "Production"))
var avg_qty = CALCULATE(AVERAGE('UID_Stock'[Qual.insp.stock of UID]),FILTER('UID_Stock','UID_Stock'[Type] = "Production"))

Return
average_qualityinspection + average_unrestricted + sum_unrestricted + sum_qualityinspection

_y = CALCULATE(AVERAGE('UID_Stock'[UID Qty)]),FILTER('UID_Stock','UID_Stock'[Check] = "In"))

2) 

UID in Open st =
var UID_ops_po = [Open POs]
var bom_qty = [_y]
Return
DIVIDE(Open POs,_y)
*****

Open POs =
var sum_oppo = CALCULATE(SUM('UID_Stock'[UID Open POs]),FILTER('UID_Stock','UID_Stock'[Type] = "Stock"))
var avg_openpo = CALCULATE(AVERAGE('UID_Stock'[UID Open POs]),FILTER('UID_Stock','UID_Stock'[Type] = "Production"))
Return
IF(NOT(ISFILTERED('Category per UID'[Category-Code])),(sum_oppo+avg_openpo))


_y = CALCULATE(AVERAGE('UID_Stock'[UID Qty)]),FILTER('UID_Stock','UID_Stock'[Check] = "In"))

3) 

Prod_Total = CALCULATE(
VAR MaxDate = MAX ( 'Invoice Date'[Date] )
RETURN
CALCULATE (
[_j],
'Invoice Date'[Date] <= MaxDate,
ALL ('Invoice Date')
)
)

****
_j = SUM('UID_Stock'[Open Qty in Var])

 

screen.PNG

 

Thanks,

KV's

2 REPLIES 2
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Could you show me what you want with a concrete example?

In addition, I can't view the photo you inset.

 

UID.JPG

 

 

Best regards

Icey

parry2k
Super User
Super User

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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