1 Reply Latest reply: Sep 5, 2017 12:42 PM by Sunny Talwar RSS

    Pivot data?  Or sum by subcategory

    Keith Bryan

      This seems easy but I just can't seem to figure it out.  What follows is sample data but the premise is exactly what I'm trying to accomplish.

       

      I have two tables, Contracts and Animals.  On the Contracts table I track all the contracts that I have to use my collection of animals, which animal is used, the total cost of food for the animal during the contract, and the date range of the contract.  On the Animals table, I track the percentage of each type of food that each animal eats.

       

      I'm trying to find total food costs by food type and Animal.  So I'd like a bar graph that sums up the total amount spent for each of the food categories (Meat, Berries, Milk, Honey, Dry Food) by Animal, by year

       

      So, for instance, I'd like to see that Bears had the following breakdown:

      Screenshot_1.jpg

       

      I am able to sum by Animal, but I can't then break it down by the percentage of food type that the animal consumes.

       

      Thanks to anyone that can help!

       

      Here are the tables:

      Contracts:

         

      Animal ID

      Contract Name

      Total Cost of Food

      Start Date

      End Date

      4Birthday Parties$120,811.009/26/189/25/19
      3Circus$114,418.139/26/169/25/17
      2Commercial$50,000.009/26/179/25/18
      1County Fair$182,522.009/26/169/25/17
      4Eating Contests$120,811.009/26/179/25/18
      2Herding$50,000.009/26/189/25/19
      1Petting Zoo$110,655.009/26/169/25/17
      4Population Control$135,471.009/26/169/25/17
      1Service Animals$185,428.009/26/189/25/19
      3Tours$110,000.009/26/179/25/18
      1Walking Service$185,428.009/26/179/25/18
      3Zoo Exhibits$110,000.009/26/189/25/19

       

      Animals

        

      Animal ID

      Animal Name

      Meat

      Berries

      Milk

      Honey

      Dry Food

      1Dog20%0%0%0%80%
      2Cat40%0%30%0%30%
      3Lion100%0%0%0%0%
      4Bear30%20%0%40%10%