Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
Thanks,
KV's
Hi @Anonymous ,
Could you show me what you want with a concrete example?
In addition, I can't view the photo you inset.
Best regards
Icey
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |