Forum Discussion

kralco626's avatar
kralco626
Frequent Visitor
8 years ago
Solved

Calculate average, std for each category

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 IDWork Type
1A
2A
3

A

4

B

5

B

 

Work IDHours
18
15
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.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

10 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    Did you created the relationship between tables in powerbi?

    • kralco626's avatar
      kralco626
      Frequent Visitor
      8 years ago

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

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      kralco626
      Frequent Visitor
      8 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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