Percentage breakdown for each subgroup

Elastic Stack Kibana
1

Hi,

I am trying to create a table in Kibana Lens (8.13) to calculate statistics for some products. In particular, for each month and product type, I would need to calculate the number of products considered and the percentage breakdown for each month (i.e. the sum of all percentages for that specific month must add up to 100).
The problem arises on this last statistic: I have tried using the formula count() / overall_sum(count()) However, overall_sum sums all products in that specific category for all the months. for instance:

ACTUAL
date | product_type | count() | count() / overall_suim(count())
2025-01-01 1 | 6 | 33%
2025-01-01 2 | 7 | 70%
2025-01-01 3 | 8 | 50%
2025-02-01 1 | 12 | 67%
2025-02-01 2 | 3 | 30%
2025-02-01 3 | 8 | 50%

EXPECTED
date | product_type | count | count() / overall_suim(count())
2025-01-01 1 | 6 | 29%
2025-01-01 2 | 7 | 33%
2025-01-01 3 | 8 | 38%
2025-02-01 1 | 12 | 52%
2025-02-01 2 | 3 | 13%
2025-02-01 3 | 8 | 35%

As you can see, for each month, the sum of the percentages is equal to 100%.
Is there a way to get these results?
Thank you,

2

Hi @mat-bro

you can track this feature request which seems similar to what you need: [Lens] make total document count available in formula · Issue #160562 · elastic/kibana · GitHub

Another option would be to upgrade and use an ES|QL to build the right table to use for the visualization.

3

Hi Marco,

Thank you for your reply. In the link you shared I saw a reference to another issue that corresponds exactly to my problem (https://github.com/elastic/kibana/issues/174510). However, it was closed as not planned.
I will try to see if it is possible to run this table with ES|QL.
Thank you!