Calculate average, std for each category

[kralco626]

I have two tables

Work:

Work ID (unique)

Work Type

Work Group

Entries:

Work ID (points to Work table)

Hours

Dollars

Basically, this represents work we do, and each entry that someone put towards the work.

Work ID	Work Type
1	A
2	A
3	A
4	B
5	B

Work ID	Hours
1	8
1	5
2	9
2	5
3	8
3	6
3	3
4	5
5	6
5	7
5	8

I want to find values such as avg, std, count etc for Work Type per Work item.

So for A the count should be 3, not 7. The average should be the average of (13,14,17) not the average of the 7 records in the entry table. Standard devision same thing, it should be based on those three values.

Similarly, the count for B should be 2, not 4.

Basically, I don't care how many entires people put towards the work. I want to know the average number of hours spent on each Work item of type A, B etc.

Seems like this is rather basic and I'm missing something, becuase I think I thought be able to accomplish this without too much effort.

[v-shex-msft · Accepted solution]

Hi @kralco626,

You can try to use below measure to get grouped average value:

Average = 
VAR idlist =
    CALCULATETABLE (
        VALUES ( 'Work'[Work ID] ),
        FILTER ( ALLSELECTED('Work'), 'Work'[Work Type] = MAX ( 'Work'[Work Type] ) )
    )
RETURN
    DIVIDE (
        SUMX ( FILTER (ALLSELECTED( Entries), [Work ID] IN idlist ), [Hours] ),
        COUNTROWS ( idlist ),
        0
    )

Regards,

Xiaoxin Sheng

[jthomson]

I'd start by grouping the rows in your entries table in Power Query so that it sums up the hours and gives you id 1, hours 13, id 2, hours 14 etc, that should make your calculations a fair bit easier

[kralco626]

Ya, I could do that. However, I would loose a lot of flexability. For example, what if the Entires table had another column, and I wanted to filter on that column and have the values updated accordingly?

[parry2k]

here is link to sample pbix 

[kralco626]

OK, so you put the Sum of hours in the hours table instead of the Work table.

That does seem to help.

However, why is the standard devision still calculated from the Hours.Hours field? Wouldn't this still give the standard devsion of the entires, not of the work items.

For example, If I filter out so I only view Work ID 5, I get a standard devision of .82. With only one work item, I should get a standard devision of 0.

[kralco626]

OK, so I think I got it to work. I created a calculated column on the Work table that just points to the Measure on the Entries table.

On Entries you added: Sum of Hours = SUM(Hours[Hours])

On the Work table I added: Hours = Hours[Sum of Hours]

Now I can use the Work.Hours field in my visualization and choose if I want sum, count, std, etc.

That seems to have worked nicely

Also, just FYI for anyone else tyring to do this, I changed the filkter type on the relationship to both, so that if the entries table is filtered, the calculations are updated as well.

[kralco626]

So I have this working in my dataset. The only hurdle is that I'm required to have the filter set to Both, and in my real data model with many tables, I can't do this for all the applicable relationships.

So this gets me one step closer, but not all the way there.

[parry2k]

glad to hear you are making progress, need to know what the relationship is with another table and how it has impact that you are not getting expected result. 

can you share picture of relationship model and the expected result?

[parry2k]

Did you created the relationship between tables in powerbi?

[kralco626]

Yes, I created a relationship between the two table in Power BI