Forum Discussion

mshodge's avatar
mshodge
Frequent Visitor
9 years ago
Solved

Sum Single Column based on Multiple Name Appearances Across Columns

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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.

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
    mshodge
    Frequent Visitor
    9 years ago

    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