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

Question & Answer
1

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

product_group/product_name/purchase_count/sold_count
fruits/apple/100/80
fruits/lemon/50/30
fruits/banana/30/10
snack/chocolate/100/20
snack/cookie/50/15

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

2

What are your calculated fields?

3

thank you for your replying.
Like this

image
image773Ă—366 10.2 KB

5

I deleted my post on solutions by mistake.
I am reposting it below.
SUM({sold_count})/SUM({purchase_count})
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