2 Replies Latest reply on Sep 20, 2018 9:13 PM by lee.jones.6

    Total count of all unique values in multiple columns

    lee.jones.6

      I have some data shaped like the following:

            

      From ColorFrom ShadeTo ColorTo ShadeReason AReason BReason 3
      BlueDarkRedDark1
      RedDarkBlueLight1
      PinkDarkRedDark 1
      OrangeLightRedDark1
      PurpleDarkOrangeLight 1
      BlueLightPinkDark1
      PinkDarkOrangeLight1
      BlueDarkOrangeDark 1
      RedLightPinkDark 1
      OrangeLightRedDark 1
      RedDarkBlueLight1

       

      I'd like to display them like the following in Tableau without having to modify the data:

         

      ColorShadeGain Reason 1Gain Reason 2Gain Reason 3Loss Reason 1Loss Reason 2Loss Reason 3
      BlueDark 11
      BlueLight3 1
      OrangeDark 1
      OrangeLight 11 1
      PinkDark1 111
      PinkLight
      PurpleDark 1
      PurpleLight
      RedDark2112
      RedLight 1

       

      So I'd like to find all of the values that exist in either the from color/shade or the to color/shade columns and list them out.  Then show the gains and losses for each of those color/shade combinations. 

       

      I cannot reshape the data.  Not all color/shades exist in both the to/from columns. 

       

      Thanks in advance,

      -LJ