2 Replies Latest reply on Aug 19, 2016 2:36 PM by Tom W

    How can I normalize data with spending coded into multiple categories for each record?

    Luke Fuller

      I received data from a City government agency with spending amounts coded into multiple fields. I've included an illustrative table below with two made-up examples of the records.

       

      Bond #DateDept 1Dept 2Dept 3Category 1Category 2Category 3Bond AmountPassed (Y-N)Description
      GOB154March 7, 2004FirePublic HealthEMSPublic HealthPublic SafetyEarthquake Safety$12,000,000Y

      Earthquake Safety Retrofit Bond

      GOB113November 2, 1999Human ServicesAdult ProbationEMSPublic SafetyGeneral Government$8,000,000Y

      Correctional Facilities and Community Transitions

       

       

      What I Have:

      A few hundred records that look like this. The data does not include actual spending disaggregated by Department or Category.

       

      What I Want:

      I'm trying to create a dashboard that visualized the amount of bond money associated with each Category. For example, if I wanted to display the bond money associated with "Public Safety", the dashboard would display $20,000,000 (the sum of the two records). But if I wanted to display the bond money associated with "General Government", the dashboard would display on $8,000,000 (only the second record).

       

      The Question:

      What can I do with this data set to get this kind of functionality? I would typically normalize this whole data set and break out the categories into duplicated records (i.e. GOB154, March 7, 2004, Public Safety | GOB154, March 7, 2004, Earthquake Safety), but this would also duplicate the Bond Amount and distort the SUM associated with each original record ("Bond #").

       

      Any recommendations? This question must come up a lot, but I cannot find any good solutions on the forums. Suggestions or references welcome!