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 |
Solved! Go to 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.
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.
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