Sum Single Column based on Multiple Name Appearances Across Columns

[mshodge]

Hopefully you can assist..  We have multiple people working on numerous packing efforts.  Their name can appear in Column 1, 2 or 3.  Column 1 is the lead and columns 2 and 3 assist.  When one person completes the job they get full credit, 2 then .5 with 3 shared at .33.

I want to show a table with the packer names across the top and the dates (data column not shown) on the left.  How do I sum a single column (Packs) when the names can appear in either column 1, 2 or 3?

Packedby1	Packedby2	Packedby3	Packs
a			1
a			1
a			1
a	b		0.5
a	b		0.5
a	b		0.5
a	c		0.5
a	c		0.5
a	c		0.5
a	b	c	0.33
a	b	c	0.33
a	b	c	0.33
b			1
b			1
b			1
b	c		0.5
b	c		0.5
b	c		0.5
c			1
c			1
c			1

[Phil_Seamark · Accepted solution]

Hi @mshodge

You could pivot the so you have the data in a tall column using the following calculated table :

Table = UNION(
            SELECTCOLUMNS('Table1',"PackedBy",'Table1'[Packedby1],"Packs",[Packs]),
            SELECTCOLUMNS('Table1',"PackedBy",'Table1'[Packedby1],"Packs",[Packs]),
            SELECTCOLUMNS('Table1',"PackedBy",'Table1'[Packedby1],"Packs",[Packs])
            )

Then you and put the Packer in a Matrix visual on columns and the rows will sum up correctly.

[mshodge]

Awesome.  Great response Phil.  Thank you.  I changed each line to Packedby1 to Packedby2 and 3.  I then filtered out the blanks (there will be many more blanks than data).

Cheers, Matt