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?
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
User | Count |
---|---|
4 | |
1 | |
1 | |
1 | |
1 |