Calculations when there is nested data

[dalogo]

I have the following data source:

Batch	Total Expense	Nested Quantity
L1023	30	8
L1023	30	9
L1024	12	10
L1024	12	10
L1024	12	11
L1025	12	20

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:

What I want is something like:

Batch	Total Expense	Nested Quantity
A	30	17
B	12	31
C	12	20
Total	54	68

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?

[Mehdi_Oudjida]

@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

I hope it helps.

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

Mehdi