locked
Weighted Average in a Power Pivot, Pivot Table Using Heirarchy RRS feed

  • 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

Answers

  • 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.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Charlie Liao Monday, May 16, 2016 7:52 AM
    • Marked as answer by Charlie Liao Thursday, May 19, 2016 1:33 AM
    Friday, May 6, 2016 3:42 AM