Skip to main content

How to calculate average for column while doing pivot table / blocks?

T

Hi! I have an issue with Airtable calculating average.

I need to present data in pivot table/block but when I use Airtable averages I see some strange numbers.

As you can see on the image below AT average is £11.78 where my average is £4.26
https://imgur.com/a/jEYXHKS

Does anyone overcome this?

How does my table look like? It’s like this below
https://imgur.com/a/d8qFIXL

I want to make pivot tables/blocks showing the performance of each variable in the column, for example, I would like to know what is the average “CRID CPR” for each variable in column “App -f…” In Google Docs I would do it like this

  1. Sum up data from column “CRID Bank Connects” with all rows with “Toast mode” and divide it by sum up of data from column “Spend” with all rows with “Toast mode”. Then I would divide sum of “Spend” by sum of “CRID Bank Connects” and this would be my average which I want to present on the graph.

Anyone can help with this?

5 replies

M
Forum|alt.badge.img+17

Hi @Tomasz_Pasko,

Statistically speaking, the problem is that you are using the Average of the Average, which does not equal to the Average.

In order to get the number you are looking for, you have to create a Rollup Field in a different table that is linked to this table in someway.

Now in order to do that, you will need to do the following:

  1. The App F… Field will not be a Single Select Field, it will be a Linked Field. So, customize the field type to Link to another record, choose create new table. Now you have a new table with the Fields Toast Mode, Daily Fort…, and Savings (it will include any other select option not shown in the screenshot)

  2. In this new table, in the 2nd and 3rd fields, choose Rollup, reference will be the 1st field (since it is linked to another table), then choose Sum CRID Bank Connects in one field and Sum Spend in the other.

  3. Now you can calculate the average as well.

If you have any questions, feel free to ask.

BR,
Mo

T
  • Tomasz_Pasko
  • Author
  • New Participant
  • 2 replies
  • February 18, 2020
Mohamed_Swella1 wrote:

Hi @Tomasz_Pasko,

Statistically speaking, the problem is that you are using the Average of the Average, which does not equal to the Average.

In order to get the number you are looking for, you have to create a Rollup Field in a different table that is linked to this table in someway.

Now in order to do that, you will need to do the following:

  1. The App F… Field will not be a Single Select Field, it will be a Linked Field. So, customize the field type to Link to another record, choose create new table. Now you have a new table with the Fields Toast Mode, Daily Fort…, and Savings (it will include any other select option not shown in the screenshot)

  2. In this new table, in the 2nd and 3rd fields, choose Rollup, reference will be the 1st field (since it is linked to another table), then choose Sum CRID Bank Connects in one field and Sum Spend in the other.

  3. Now you can calculate the average as well.

If you have any questions, feel free to ask.

BR,
Mo


Hi Mohamed, thanks for the reply.

So I did it like this

https://imgur.com/a/VN8y25c
https://imgur.com/a/eDCPIqR
https://imgur.com/a/UApHz0W

Is this what you meant?

Also, how can I put this average into proper data and visualize it? I don’t get this part.

M
Forum|alt.badge.img+17
Tomasz_Pasko wrote:

Hi Mohamed, thanks for the reply.

So I did it like this

https://imgur.com/a/VN8y25c
https://imgur.com/a/eDCPIqR
https://imgur.com/a/UApHz0W

Is this what you meant?

Also, how can I put this average into proper data and visualize it? I don’t get this part.


Hi Tomasz,

Yes, that is what I meant. Just to be clear, the sums you now have in the Rollup Fields are for the Fields you chose in the formula and for the Records that are in the PRISM copy Field. So are the records linked correct? Or are you looking to get the average of something else?

T
  • Tomasz_Pasko
  • Author
  • New Participant
  • 2 replies
  • February 20, 2020

Hi Mohamed, it works, but I can’t create dynamic blocks now. Would you be able to help us to execute it? We pay you for this if needed :grinning_face_with_big_eyes:

Can you please email me with dates you’re able to meet at tomasz@paintgun.io

M
Forum|alt.badge.img+17
Tomasz_Pasko wrote:

Hi Mohamed, it works, but I can’t create dynamic blocks now. Would you be able to help us to execute it? We pay you for this if needed :grinning_face_with_big_eyes:

Can you please email me with dates you’re able to meet at tomasz@paintgun.io


Hi @Tomasz_Pasko,

Thanks for the offer :grinning_face_with_big_eyes: I believe it is too simple to be paid for :grinning_face_with_big_eyes:

If you want, please send me a View link to your Base and I will check it out for you no issue. mohamedswellam@hotmail.com

BR,
Mo

Reply


Most helpful members this week

Terms & Conditions