Blended Data - Aggregation / Field Type

[juan_pdb]

Hi everyone, 

I'm blending 2 sources, in a left outer join being the left table:

Product code | Name | Guests

and the right table:

Product code | Expenses

I'm joining the tables by using the key 'Product code' and I want to add up Expenses (Sum, metric).

However, I see that I am also adding guests for every line of expenses that I have in my data source. Example: A product code with 11 guests, and 3 lines of input for expenses, will show up as 33 guests.

Can you tell me what I'm doing wrong? I tried adding 'Guests' as a dimension but the problem is that I want to treat it as a number and obtain a 'Total sum' of guests in the end, so that shouldn't work. Is it a problem with my joins maybe? Thanks!

[Looker_Studio_B]

On the left table, add the metric Record Count, which as its name says counts the number of records (or a calculated field just inputting 1, does the same).

Now, change your formula to SUM(Expenses)/SUM(Record Count). Back to your example, your 33 guests will be divided by 3, hence back to 11.

[juan_pdb]

Thanks a lot @Looker_Studio_B this was really helpful! I can do now a line-by-line guest count or expenses as intended.

However, when I try to aggregate data in a scorecard, this is no longer valid. Let's say, calculating the "Expenses per guest" (this being total expenses / total guests).

Seems like the custom formula aggregates the record count for all inputs and divides by the sum of all of them, instead of calculating on a line-by-line basis.

How shuold I do this? Thanks again

[阿泰]

Hi, try this function in the custom field:
COUNT(Guests) / SUM(Expenses)

[Looker_Studio_B]

So, just for the scorecard aggregated, doing directly SUM(Expenses/Guests) should work, no?

[阿泰]

No, you have to break it down like below:

COUNT(Guests) / SUM(Expenses)