Aggregating Across Common Values

[bnevers]

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. 

[AlB · Accepted solution]

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  

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[bnevers]

Perfect - thank you!!

[AlB · Accepted solution]

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