4 Replies Latest reply on Jul 26, 2016 2:25 PM by Michael Peterson

    Create rates by dividing dimensions by a specific measure value

    Michael Peterson

      Hello! This issue seems like it has to have been asked before, and perhaps how I'm describing the issue is the problem, but I've not found a solution while scouring old questions/answers.

       

      My data are counts of firms in my state by different demographic characteristics for the owners. My data are structured as:

       

            

      Veteran StatusGenderRaceHispanic OriginYearNumber of firms with or without paid employees
      AllAllAllAll1997410,634
      AllFemale-ownedAllAll1997108,417
      AllMale-ownedAllAll1997216,926
      AllEqually male-/female-ownedAllAll199776,675

       

       

      I'd like to calculate the share of all business owners each group has. In the case of female-owned businesses in 1997 you'd calculate 108,417 / 410,634 to accomplish that but my issue is that the denominator is different each year (businesses close, new businesses open), so that what I really need is a calculated field(s) that would apply that division to all of my groups based on the firm total for a given year, so that its grabbing the right numerator and denominator from each row:

       

      1997 women / 1997 total

      1998 women / 1998 total

      1999 veterans / 1999 veteran total

      Etc

       

      Any ideas? Thanks in advance for your wisdom!