Announcements
cancel
Showing results for
Did you mean:
Not applicable

Pivot data? Or sum by subcategory

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:

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%
1 Solution

Accepted Solutions
MVP

It would be better to transform the animal table using The Crosstable Load. Once you do that, all you would need is to use the new field as your dimension and multiple the value with your expression