7 Replies Latest reply on Nov 12, 2018 10:00 PM by Rohit Sharma

    Sum in Text Table Dimension wise

    Rohit  Sharma

      Hello All,
      I need to show AverageWage (Total Wage aw*1000/Total Employees aw) in addition to existing MSA Abb (as shown in Image 1) values I need to add rows like Raleigh-Durham (Raleigh + Durham) and Tampa Bay Combined (Lakeland+North Port+Homosassa+TampaBay)

       

      I have attached Test.twbx with this discussion.

       

      When I am applying IF condition for creating groups (Image2) it is not showing the MSA abb exixsting values but only the grouped one's also the IF condition (Mentioned below) adding up the AverageWage values

      For instance, for Raleigh-Durham Average wage should be  = (Total Wage aw Raleigh + Total Wage aw Durham) /  (Total Employees aw Raleigh + Total Employees aw Durham) =  55453.93865 but its is showing 113421.89


      Condition used for MSA Group (Image 2)

      IF ([MSA Abb] = 'Durham' or [MSA Abb] = 'Raleigh')

      THEN 'Raleigh-Durham'

      ELSEIF ([MSA Abb] = 'Lakeland' or [MSA Abb] = 'North Port'

      or [MSA Abb] = 'Homosassa' or [MSA Abb] = 'Tampa Bay')

      THEN 'Tampa Bay Combined'

      ELSE [MSA Abb]

      END

       

       

      Expectation:

      In addition to all the existing dimension values of MSA abb (Image 1) it should show the extra 3 rows which would be

      1. Raleigh-Durham (MSA abb = Raleigh + Durham)

      2. Tampa Bay Combined (MSA abb =Lakeland+North Port+Homosassa+TampaBay)

      3. US average (which is all the MSA Total Wage aw/Total Employees aw)

       

       

      Image1:

       

       

      Image2:

       

       

      Please let me know how can we do it in Tableau.
      Appreciate your help!