Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
---|---|---|---|---|
4 | Birthday Parties | $120,811.00 | 9/26/18 | 9/25/19 |
3 | Circus | $114,418.13 | 9/26/16 | 9/25/17 |
2 | Commercial | $50,000.00 | 9/26/17 | 9/25/18 |
1 | County Fair | $182,522.00 | 9/26/16 | 9/25/17 |
4 | Eating Contests | $120,811.00 | 9/26/17 | 9/25/18 |
2 | Herding | $50,000.00 | 9/26/18 | 9/25/19 |
1 | Petting Zoo | $110,655.00 | 9/26/16 | 9/25/17 |
4 | Population Control | $135,471.00 | 9/26/16 | 9/25/17 |
1 | Service Animals | $185,428.00 | 9/26/18 | 9/25/19 |
3 | Tours | $110,000.00 | 9/26/17 | 9/25/18 |
1 | Walking Service | $185,428.00 | 9/26/17 | 9/25/18 |
3 | Zoo Exhibits | $110,000.00 | 9/26/18 | 9/25/19 |
Animals
Animal ID | Animal Name | Meat | Berries | Milk | Honey | Dry Food |
---|---|---|---|---|---|---|
1 | Dog | 20% | 0% | 0% | 0% | 80% |
2 | Cat | 40% | 0% | 30% | 0% | 30% |
3 | Lion | 100% | 0% | 0% | 0% | 0% |
4 | Bear | 30% | 20% | 0% | 40% | 10% |
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
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