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 Per Widget]),ALLEXCEPT(Table1,Table1[Widget#])).
 
'Hours Per Widget' is a DIVIDE function of 2 measures, 'Total Hours' (numerator) & 'Average Quantity' (denominator), which are themselves a "SUM" function column 'Hours' & an AVERAGE function of 'quantity' .
 
My table is somewhat organized like this: 
 
Project# | Widget# | WidgetOrder# | Quantity | Hours | 
 
This is what I need to do:
 
1) Add up all the hours for WidgetOrder#X. 
2) Divide up those hours by an average of all the quantity numbers for WidgetOrder#X.
3) That gives me an average hours per widget for that specific WidgetOrder#X.
4) Then I need to group those by Widget# and average them by that. 
5) Lastly, I need to keep the sample size limited to that Project#. 
 
Any ideas? 
 
My formula above ALMOST WORKS, but it gives me the average for ALL Widget#'s in my database, not just for that Project#.
Need Help

3 Replies

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