How to deal with average time duration bucketed by hour

[wilburn]

I have 5 APIs (/api/v1/a, /api/v1/b, etc.).

Each hour, I get the average time it takes for requests to those APIs to complete.

Hour	API	Requests	Duration
10	/api/v1/a	5	3 sec
10	/api/v1/b	10	3 sec
11	/api/v1/a	1000	100 ms

In this table, I cannot do a straight average for duration  on /a as this would yield a very skewed result; most requests are in the 100 ms category but a naive simple average would show 500+ ms for the duration.

What are the best approaches to showing average duration (time spans) that provide better accuracy in Power BI?  Is this something that is handled through weighting formulas written in Power Query or DAX?

Really it would be better (?) to expand 1000 values of 100 ms in hour 11 into discrete values and then use those to average.  I don't think that is an option in Power BI.

What are approaches that more veteran BI people than myself use to handle these scenarios?

TIA!

[Greg_Deckler · Accepted solution]

@wilburn So like this?

Measure = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "Value",[Requests] * [Duration]
        )
RETURN
    DIVIDE(SUMX(__Table,[Value]), SUM('Table'[Requests]),0)

[wilburn]

Thank you @Greg!

For hundreds of millions of rows for some of the APIs, this formula was really close to the actual production data query results.  Nice job!

I am ordering a copy of your book!  

Thanks for the work you do here.