Forum Discussion
creating matrix with average value multi groupped
Hi all,
Currently I've a table construct by merging other data sources. In this table a part is interesting to have a view of staffing a team.
This table contains a date, an user id, a team, a work's period and a weekday. Something like this :
table1
| date | id_people | team | shiftPeriod | weekday |
| 01/01/1970 | ABC_123 | A | 5-13 | Monday |
| 01/01/1970 | XYZ_456 | B | 5-13 | Monday |
| 01/01/1970 | DEF_456 | A | 5-13 | Monday |
| 01/01/1970 | RST_789 | C | 13-21 | Monday |
| 02/01/1970 | RST_789 | B | 13-21 | Tuesday |
| 02/01/1970 | RST_789 | A | 5-13 | Wednesday |
| 02/01/1970 | XYZ_456 | B | 13-21 | Wednesday |
| 02/01/1970 | DEF_456 | A | 13-21 | Wednesday |
| 02/01/1970 | GHJ_098 | D | 21-5 | Wednesday |
| 03/01/1970 | ABC_123 | A | 21-5 | Thursday |
| 03/01/1970 | XYZ_456 | A | 21-5 | Thursday |
| 03/01/1970 | DEF_456 | B | 21-5 | Thursday |
| 03/01/1970 | RST_789 | B | 5-13 | Thursday |
| 03/01/1970 | ABC_987 | D | 13-21 | Thursday |
| 03/01/1970 | QSD_345 | C | 21-5 | Thursday |
| 03/01/1970 | LMP_765 | C | 13-21 | Thursday |
| 03/01/1970 | KJH_592 | C | 13-21 | Thursday |
| 03/01/1970 | BBN_886 | C | 21-5 | Thursday |
| 04/01/1970 | ABC_123 | A | 13-21 | Friday |
| 04/01/1970 | BBN_886 | B | 21-5 | Friday |
| 04/01/1970 | LMP_765 | C | 13-21 | Friday |
| ... | ... | ... | ... | ... |
My goal is to obtain a matrix view where columns show the team, and in each row I get the average number of people group by work's period and by week day.
Something like that :
Im' blocked and don't know how I can attack this problem. Not a problem to create a matrix with a total, but here I'd like the average for each weekday based on all the same weekday in the showing year.
How can I solve it?
a verry thanks to who can help me
efhache , try measure like
averageX(Sumamrize(table, Table[weekday], "_1", count(Table[id_people])), [_1])
3 Replies
efhache , try measure like
averageX(Sumamrize(table, Table[weekday], "_1", count(Table[id_people])), [_1])
Thans amitchandak for the reply, but each row is a total, not an average. And I want to get the average of present people for a monday, regardin all the monday of the year.
So if we consider One monday on three is 4 peoples and other is 1... the value must be something like 1.98 people... and not 103...finally gain what I need by adding the number of week for the period. Thanks a lot
