Access average of a column in calculated fields

[HerculesVent]

I have this table that shows the data from a dataset in google sheets. Now, what i want to do is make an extra column on this table, that basically does this:
If the helps of the user are above the average of all the user's help, the field shows "1", otherwise, it shows 0. I have tried to do this myself but i can just not get it to work. Any help is appreciated. Thanks!

[lauratilton]

You need to be able to pull the user’s average helps, and then use it in the calculated field. BUT if you want to do that in LS, you’d need a blend to do the first part which makes the second part tricky. I would use AVERAGEIF in Sheets to create a new field of the user’s average helps. You can then create a simple calc field in the LS data source to do the comparison (or you could do that in Sheets too). 

[HerculesVent]

i need all the calculations to be done in looker studio sadly

[HerculesVent]

Could you tell me how i could do that blend?

[lauratilton · Accepted solution]

Put all your fields in the first table of the blend. In the second table of the blend just put the Helps metric, with AVG aggregation. Choose cross join and rename Helps to Overall Helps Avg for that second table.

That will give you a blended data source that contains all your fields plus the Overall Helps Avg field. If you want to use it in a calculation you’ll need to create a calc field at the chart level, not the data source level. 

[ArkadyZagdan]

@HerculesVent I AM not sure its doable without a custom query form a database. Something similar you can achievie by creating a Field with average and then using a conditional formating and instead showing 1 or 0 just change a color based on the result.

[HerculesVent]

Yea what i am trying to do is make a field with the average of the whole column. Thats what i cant figure out how to make. If i just select average here:

It just doesnt work, since only 1 entry is for that row. I want a field to have the average of the whole column.

[ArkadyZagdan]

As a general rule of thumb always seek to do transformation and aggregation steps in your original data source. According to my knowledge there is no way to achieve this in LS directly. While its reporting capabalities are decent the data transformation options here are really limited.  LS was designed to focus on the front-end mostly. Thhe formulas here are bit limited but folr anyone using BigQuery as a data source it is never a problem.