How to perform an average of an average?

[djanszentql]

I have a very simple dataset with two columns.  Instead of just taking the average (in the case below.. 9.4), I would like to take the averages of each ProcName and THEN average those together. (in the case below.. 9.167).

ProcName	TotalMilliseconds
Proc1	5
Proc1	10
Proc2	8
Proc3	4
Proc3	20

[nandic · Accepted solution]

Hi @djanszentql ,

Attached file with calculation.




Here is the result:

Average per product =
VAR _productAvg =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[ProcName] ),
        "_Average", CALCULATE ( AVERAGE ( 'Table'[TotalMilliseconds] ) )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[ProcName] ),
        AVERAGE ( 'Table'[TotalMilliseconds] ),
        AVERAGEX ( _productAvg, [_Average] )
    )

Regards,
Nemanja Andic

[djanszentql]

If I wanted to add a filter on date such as this:

DATESBETWEEN('avg milliseconds roll up'[Date], MAX('avg milliseconds roll up'[Date]) - 29, MAX( 'avg milliseconds roll up'[Date]) ) )

Where would I go about adding that in the formula above?

[nandic]

Hi,
If it will be static filter (always Max date - 29 days) you can create calculate column (in my example "DatesBetween" -data type WholeNumber) and use this column to either filter data staticly or dynamically inside measure calculation.
Example static: you can add this new calculated column to Filters pane (filter for whole page, filter for whole report, filter for visualization) and to define which DatesBetween value to display.

Example dynamic: 
On DAX formula from previous reply, you can add new condition using FILTER function. 
Ie: calculate(average(TotalMiliseconds), filter(table, DatesBetween > 0))

If it doesn't help could you send pbix file with dummy data, but with same structure as as (same tables, relationships, data types).

Regards,
Nemanja Andic

[djanszentql]

It will be static so this should work!!  Thanks for all your help!

One last question I have (and please let me know if I should make a separate post for this because I keep expanding the scope of the initial question for this thread).. How could I turn this into a weighted average where the third column is ExecutionCount.  To clarify, if Proc1 had 10,000 exeuctions, but Proc2 had 5,000.. I would want to apply more weight to Proc1 in the average calculation.  Again, please let me know if I should make a separate post for this.  I appreciate your help thus far!

[nandic]

You can keep it here as well, until we find complete solution 🙂
Could you just send expected results in Excel format (no need to upload file to dropbox/google drive, just paste here dummy values)? When i see dummy data and expected results it will be easier to reply.

[djanszentql]

It won't let me attach the excel file, but here are the values/calculation:

[nandic]

Thanks for provided details, attached updated version of the pbix file.

Weighted Average Measure =
VAR _Sumproduct =
    SUMX ( 'Table', 'Table'[TotalMilliseconds] * 'Table'[ExecutionCount] )
VAR _Sum =
    SUM ( 'Table'[ExecutionCount] )
RETURN
    DIVIDE ( _Sumproduct, _Sum )

Regards,
Nemanja Andic