How to dynamically aggregate data in a pivot table as it is collapsed/expanded

I have the following raw data to manage the initial number of products in stock and those that have already been sold


From here, I would like to create a report where I can see the inventory digestion rate (sold_count/purchase_count) at both the product_group granularity and the product_name granularity.

The use case would be "I see a trend that the inventory digestion of fruits as a whole is deteriorating in the spring, and I want to find out which fruits in particular are deteriorating.

I have created a custom calculated column defined by sold_count/purchase_count and a pivot table that can be collapsed by product_group/product_name granularity respectively. However, when I collapse by product_group, the SUM of sold_count/purchase_count is displayed and it does not work.

What should I do?
(I tried to do it with a table with drill-down function instead of a pivot table, but it didn’t work because “add drill-down layer” is not shown in field wells, only “replace”…)

I’m not good at English, so sorry if it’s hard to understand. This question was also translated by DeepL

What are your calculated fields?

thank you for your replying.
Like this

I deleted my post on solutions by mistake.
I am reposting it below.
It worked fine when I described the
The data preview says it is unavalable, but when I put this column in a pivot table in the visualization function, it works!

1 Like