Forum Discussion

bnevers's avatar
bnevers
Regular Visitor
6 years ago
Solved

Aggregating Across Common Values

I have timecard data in a PowerBI table that looks like this:

 

Staff     Project     Hours

A          1000          2

A          2000          4

A          3000          2

B          1000          3

B          2000          1

B          4000          3

 

I would like to create a table that sums the hours each individual spent working on common projects with each other individual. In this case, A worked on projects 1000 and 2000 with B and vice versa. So the summary table would look like this:

 

Staff       A                 B

A            -               2+4 = 6

B            3+1=4        

 

Is there a way to do this with DAX? Any help is appreciated. 

Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi bnevers 

    Try this

    1. Place Table1[Staff] in a table visual 

    2. Place this measure in the visual

    Measure =
SUMX (
    Table1;
    IF (
        CALCULATE (
            COUNT ( Table1[Project] );
            ALL ( Table1[Hours] );
            FILTER (
                ALL ( Table1[Staff] );
                Table1[Staff] <> SELECTEDVALUE ( Table1[Staff] )
            )
        ) <> 0;
        Table1[Hours]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi bnevers 

    Try this

    1. Place Table1[Staff] in a table visual 

    2. Place this measure in the visual

    Measure =
SUMX (
    Table1;
    IF (
        CALCULATE (
            COUNT ( Table1[Project] );
            ALL ( Table1[Hours] );
            FILTER (
                ALL ( Table1[Staff] );
                Table1[Staff] <> SELECTEDVALUE ( Table1[Staff] )
            )
        ) <> 0;
        Table1[Hours]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut