Forum Discussion

powerboss's avatar
powerboss
Frequent Visitor
6 years ago

How To Calculate Average Of Averages

So this is essentially the formula I'm writing which has come the closest:   Project Average Hours Per Widget = CALCULATE(AVERAGEX(SUMMARIZE(Table1,Table1[project#],Table1[WidgetOrder#]),[Hours Pe...
Need Help
edhans's avatar
edhans
Community Champion
6 years ago

Without data to play with, will be hard to do, but your ALLEXCEPT() function is telling it to ignore Project#.

 

Here it is formatted, to clearly see:

Project Average Hours Per Widget =
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            Table1,
            Table1[project#],
            Table1[WidgetOrder#]
        ),
        [Hours Per Widget]
    ),
    ALLEXCEPT(
        Table1,
        Table1[Widget#]  <----- Telling it to ignore everything but Widget#
    )
)

 

  • powerboss's avatar
    powerboss
    Frequent Visitor
    6 years ago

    Basically I need that 'Hours Per Widget' number, but I then need to average them, grouped by Widget#. And I simultaneously need that average sample size to  be contained within the Project#. 

     

    All of these are calculated columns that I am pulling from other tables via LOOKUPS. 

     

    Any suggestions?

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      It will be very difficult to assist without data. I or others here could spend an hour mocking up a model that I think matches your description, and 100% miss the boat. I've unfortunately done that one too many times.