[LENS] Allow to access metric for several levels to compute metrics

[vsabelli]

Hi all,

while creating a table visualisation with Lens, we often need to split metrics on several levels (e.g. to compute a percentage).

For instance, let's suppose we have such documents on Elasticsearch:
{
"name": "John",
"surname": "Smith"
"customer_id": "123abc"
"message": "hello"
}
and that we want a table with the following data:

---

surname | unique customer id | name | unique customer id | percentage |

Smith | 8 | John | 5 | 5%
Smith | 8 | Mark | 2 | 2%
Smith | 8 | Lucas | 1 | 1%
Doe | 92 | John | 92 | 92%

Percentage is calculated over unique count by customer_id to estimate the name/surname distribution in our unique customers (we might have many documents with same name, surname, customer_id but different message).

In spite of the huge power of formulas that we can leverage in Lens tables, I am not able to compute such percentage (I cannot reference a previous column, nor a "upper layer" aggregation).

This feature is already present in an external plugin (Enhanced table), but we would like to migrate to a solution built-in to Kibana (and Lens really fits our needs!).

Are we missing any Lens feature that might help us computing the required values?

Thank you for your support!!

[elasticmachine]

Pinging @elastic/kibana-vis-editors @elastic/kibana-vis-editors-external (Team:VisEditors)

[flash1293]

This feature unfortunately doesn't exist yet, it's captured in #94609 Left a +1 there