How to get an average of partial data?

Question

The demo table&nbsp;is like:ID &nbsp; &nbsp;UserCode &nbsp; &nbsp;DefaultHours&nbsp; &nbsp; &nbsp;TeamCode &nbsp; Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Hours1 &nbsp; &nbsp; &nbsp;A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-31 &nbsp; &nbsp;4.52 &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-31 &nbsp; &nbsp;5.03 &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-31 &nbsp; &nbsp;2.54 &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-30 &nbsp; &nbsp;5.05 &nbsp; &nbsp; &nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-30 &nbsp; &nbsp;4.06 &nbsp; &nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-29 &nbsp; &nbsp;5.57 &nbsp; &nbsp; &nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-29 &nbsp; &nbsp;2.08 &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; T2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-29 &nbsp; &nbsp;2.09 &nbsp; &nbsp; &nbsp;E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6.0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017-05-29 &nbsp; &nbsp;2.0NOTES: Different users in different days may have different Hours value, but eash user's&nbsp;DefaultHours is the same in all days.&nbsp;How to create two new measures to get the two teams' average&nbsp;DefaultHours dynamically?For example:1). For the above 8 rows:&nbsp; &nbsp; &nbsp;Team T1's average&nbsp;DefaultHours should be (5+4+6)/3&nbsp; &nbsp; &nbsp;Team T2's average&nbsp;DefaultHours should be (2+3)/22). If Date&nbsp;2017-05-29 was selected, then only 4 rows&nbsp;left:&nbsp; &nbsp; &nbsp;Team T1's average&nbsp;DefaultHours should be (5+6)/2&nbsp; &nbsp; &nbsp;Team T2's average&nbsp;DefaultHours should be (2+3)/2&nbsp;Thanks

gilbertq · Answer

Hi&nbsp;j_w&nbsp;You could possibly do the following measures below.&nbsp;Total Default Hours = sum('TableName'[DefaultHours])

Total Rows = COUNTROWS('TableName')

Avg Default Hours = DIVIDE([Total Default Hours],[Total Rows])

j_w · Answer

Hi&nbsp;GilbertQ&nbsp;Thanks for the reply.&nbsp;Your method will get a different result, and is not working for team T1 and T2 respectively.&nbsp;For&nbsp;example 1), the result from your method will be&nbsp;(5+4+...+3+6)/9 which is not equal to&nbsp;&nbsp; &nbsp; &nbsp;Team T1's average&nbsp;DefaultHours should be (5+4+6)/3, or&nbsp; &nbsp; &nbsp;Team T2's average&nbsp;DefaultHours should be (2+3)/2&nbsp;

gilbertq · Answer

Hi&nbsp;j_w&nbsp;Based on your initial dataset there are 9 rows for T1?&nbsp;Or did I miss something?

j_w · Answer

Hi&nbsp;GilbertQ&nbsp;ID 3, 7, 8 rows are&nbsp;under T2.

gilbertq · Answer

Hi&nbsp;j_w&nbsp;Yes that then looks correct for me if you look at the table and select T2 in the columns?

Forum Discussion

How to get an average of partial data?

6 Replies

Related content

Power BI data sources showing Partial Online and Offline

kusto error- partial query failure

CubeValue formula to data model - only works partially

Average Aggregated Data

Average and Moving Average