i am doing some calculations where i have 2 tables.
table 1 have columns product, process, subprocess and volume.
table 2 have columns product, process, subprocess and query count
i want to calculate percentage of query count in respect to volume. But i want to take denominator(volume) till process level only.
this means if i select any product then product then it should take numbers from query count for that particular process but volume number should be product wise not process wise. like sum of volume for product A is 10 and it has process x and y with volume 4 and 6. in table 2 query count for x and y is 2 and 3. so the % calculation should be 2% and 3%. for this i have expression sum(query count)/sum(total volume).
Now when go another level down and click any process, the calulation changes as it takes volume for that particular process only as denominator. i want to restrict it to product level.