Efficiency Calculation + Relationship

[Anonymous]

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)

[amitchandak]

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])))

[Anonymous]

unfortunately it didnt work. for some reason i cannot create the summarize formula. maybe because its a direct query to a manual table?