Forum Discussion
Efficiency Calculation + Relationship
Hello,
I been trying to create a computation of our company efficiency and I am really stuck on this.
Not sure if my relationship of table are correct or maybe the solution is just simple and Im over thinking this.
This is a Direct Query connection so I'm kinda limited with formula to use.
Computation Formula
Efficiency = Box Count / Output
Output = TargetNew x Machine x Minutes
TargetNew = Target/Conversion
Location/Variables
Box Count - located at "Box Table" table.
Machine - located at "Efficiency Table" table.
Minutes - Basically just minutes for that hour (these are the one that gives me issues, you can see below)
Target - located at "Efficiency Table" table.
Convertion - located at "Conversion" table.
As of now here's what i have in powerBI
*is there a way to show the full minutes? see my excel file below (pics)*
Compare to my excel file which is the correct calculation.
Difference/Variance
- Minutes - you can see that theres a variance with my powerbi and excel file. Basically I want to put the full minutes, if its not possible, as long the grand total is correct. that would work.
- See red highlight? - if there are two or more Order in an Hour it should just Average those in that hour.
Below show what we want to show in powerBI.
I hope it make sense. I really appreciate any help.
Heres the PBIX file PBIX FILE
Box Table and Order Detail are in direct query (In my Original File)
2 Replies
Not tested on your file. This is something that can be tried
Box Count = Sum('Box Table','Box Table'[Box Count]) Sum Minute = Sum('Box Table','Box Table'[Minute]) Box Per Min = sumx('Box Table' ,Divide('Box Table','Box Table'[Box Count],'Box Table'[Minute])) Or Box Per Min = sumx('Box Table' ,Divide(sum('Box Table','Box Table'[Box Count]),Sum('Box Table'[Minute]))) Efficiency = AverageX(summarize ('Order Details','Order Details'[Line],Conversion[Convertion],'Efficiency Table'[Machine],'Efficiency Table'[Target],"_box",[Box Per Min ]) , divide([_box],(Divide([Target],[Convertion])*[Machine]))) Efficiency = AverageX(summarize ('Order Details','Order Details'[Line],Conversion[Convertion],'Efficiency Table'[Machine],'Efficiency Table'[Target],"_box",[Box Count],"_min",[Sum Minute]) , divide([_box],(Divide([Target],[Convertion])*[Machine]*[_min])))
