Calculations when there is nested data

I have the following data source:

BatchTotal ExpenseNested Quantity
L1023308
L1023309
L10241210
L10241210
L10241211
L10251220

The source has nested data, so it shows me the Total Expense for each batch (30, 12 and 12 for A, B and C respectively) and also shows me the amounts in one of the nested fields (they add up to 17,  31 and 20).

As expected, when I make a table it shows me the following:

Captura de pantalla 2024-10-12 a la(s) 6.27.17 a.m..png

What I want is something like:

 

BatchTotal ExpenseNested Quantity
A3017
B1231
C1220
Total5468

I have tried with MAX or AVG, but the problem is that the totalization works incorrectly.

How can I solve the problem?

Is it possible to use calculated fields to correctly group each of the fields?

0 1 219
1 REPLY 1

@dalogo 

Here one of the possible solution, applicable directly in Looker Studio without changing the data model of your data source.

You need to create a single-table data blending like the example below and use it as your chart data source.
The metric "Deduplicated Total Expense" is a calculated field with the following formula:
SUM(Total Expense)/Record Count

Mehdi_Oudjida_2-1728796703392.png

I hope it helps.

Feel free to tell me if you need more info, otherwise you can accept this answer as a solution. 

Mehdi