How to get an average of partial data?

[j_w]

The demo table is like:

ID    UserCode    DefaultHours     TeamCode   Date               Hours

1      A                  5.0                       T1                  2017-05-31    4.5

2      B                  4.0                       T1                  2017-05-31    5.0

3      C                  2.0                       T2                  2017-05-31    2.5

4      A                  5.0                       T1                  2017-05-30    5.0

5      B                  4.0                       T1                  2017-05-30    4.0

6      A                  5.0                       T1                  2017-05-29    5.5

7      C                  2.0                       T2                  2017-05-29    2.0

8      D                  3.0                       T2                  2017-05-29    2.0

9      E                  6.0                        T1                  2017-05-29    2.0

NOTES: Different users in different days may have different Hours value, but eash user's DefaultHours is the same in all days.

How to create two new measures to get the two teams' average DefaultHours dynamically?

For example:

1). For the above 8 rows:

     Team T1's average DefaultHours should be (5+4+6)/3

     Team T2's average DefaultHours should be (2+3)/2

2). If Date 2017-05-29 was selected, then only 4 rows left:

     Team T1's average DefaultHours should be (5+6)/2

     Team T2's average DefaultHours should be (2+3)/2

Thanks

[GilbertQ]

Hi @j_w

You could possibly do the following measures below.

Total Default Hours = sum('TableName'[DefaultHours])

Total Rows = COUNTROWS('TableName')

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

[j_w]

Hi @GilbertQ

Thanks for the reply.

Your method will get a different result, and is not working for team T1 and T2 respectively.

For example 1), the result from your method will be (5+4+...+3+6)/9 which is not equal to 

     Team T1's average DefaultHours should be (5+4+6)/3, or

     Team T2's average DefaultHours should be (2+3)/2

[GilbertQ]

Hi @j_w

Based on your initial dataset there are 9 rows for T1?

Or did I miss something?

[j_w]

Hi @GilbertQ

ID 3, 7, 8 rows are under T2.

[GilbertQ]

Hi @j_w

Yes that then looks correct for me if you look at the table and select T2 in the columns?

[j_w]

Hi @GilbertQ

I guess my question is not easy to catch 🙂

Go back to the dataset, the preferred values for following example:

1). For the above 9 rows:

     Team T1's average DefaultHours should be (A's DefaultHours+B's DefaultHours+E's DefaultHours)/(Count of T1 Users), which is (5+4+6)/3=5.0

     Team T2's average DefaultHours should be (C's DefaultHours+D's DefaultHours)/(Count of T2 Users), which is (2+3)/2=2.5

Your method:

1). For the above 9 rows:

     Team T1's average DefaultHours will be T1's total hours/rows, which is (5+4+5+4+5+6)/6=4.83

     Team T2's average DefaultHours will be T2's total hours/rows, which is (2+2+3)/3=2.33

Sorry about so many numbers in the above words, but hope I made it a little clearer.

Thanks