Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
@wilburn So like this?
Measure =
VAR __Table =
ADDCOLUMNS(
'Table',
"Value",[Requests] * [Duration]
)
RETURN
DIVIDE(SUMX(__Table,[Value]), SUM('Table'[Requests]),0)
@wilburn So like this?
Measure =
VAR __Table =
ADDCOLUMNS(
'Table',
"Value",[Requests] * [Duration]
)
RETURN
DIVIDE(SUMX(__Table,[Value]), SUM('Table'[Requests]),0)
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.
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |