# Weighted Average in a Power Pivot, Pivot Table Using Heirarchy • ### Question

• All:

I was trying to create a weighted average metric based on a number of hours a machine was running.  However, when you generate the power pivot table, it wants to SUM the percentages of a machines performance vs. weighting them.

I use the following:

Weighted Actual:=SUMX(OEE,[SumOfTheoreticalRuntime])/[Total Theo]*SUMX(OEE,[OEE-ACT])

It would be the equivalent of a machine at a locations hours, divided by the total hours run at the plant, * the actual performance.

Any ideas?

Thursday, May 5, 2016 8:47 PM

• What you have does not really look right as a weighting calculation.

You effectively have  SUM(A) / B * SUM(C)  so if [OEE-ACT] is some sort of weight then you are summing them before you multiply them which is wrong, but I don't really understand your last sentence and which concept there maps to the columns/measures in your expression.

Can you maybe illustrate this with some example data for 3-4 machines.

If we have it in the form of:

* given the following data...
* I want to apply the the calculation logic of ....
* which I expect to return a result of ...

Then myself or someone else here can quickly put the sample data into PowerPivot and test the sort of expression that we think would work.