Count All + Percentage of Total

[Rachael_Castela]

Hey there,

I’m looking to capture:

1. Total Number of Vendors (1st column) that are in each Lifecycle Stage (Ex: 2 in PO Launched, 1 in PO Placed) and
2. The % of vendors relative to the total (Ex: 40% of all vendors have a Lifecycle Stage of PO Launched)

Questions:

1. Does Lifecycle stage need to be a linked record? For my case it doesn’t (although that’s how it’s shown in the screenshot)
2. Can that live in this table, or will it need to live in a separate, linked table?

Thank you!

[Jonathan_Katz]

Hi there! I think I can help.

You’ll definitely want Lifecycle to be a linked record because it will enable you to solve the first “capture”!

(I wrote a long explanation here because I thought you would like some extra info on linked records and how to use them - not trying to assume what you know or don’t know!)

Since linked records link in both directions, that is, Vendor has a linked record field for Lifecycle, that means Lifecycle also links back to Vendors!

In other words, on your table of Lifecycles, you’ll be able to see the linked Vendor records from the Vendors table.

Check out the Lifecycle table:

It’s showing you the exact opposite! Now it’s saying “hey I’ve GOT the Lifecycle, show me which vendors have it!”

Since Vendor A and Vendor B are both in Lifecycle 1, you can see on the Lifecycle table, that the record for Lifecycle 1 has two linked vendors - Vendors A and B, as expected. Lifecycle 2 has Vendor C, Lifecycle 3 has Vendor D, and Lifecycle 4 has no Vendor, because none of the vendors (on the vendors table) were in Lifecycle 4.

---

Still on the Lifecycle table, let’s use a Count field to count those vendor linked records on the Lifecycle field -
This is as simple as creating a new field, selecting the Count option, and having it count the field with the linked records

Now, you can see each Lifecycle record has a new field, which is counting the number of vendors that link to that Lifecycle. In other words, how many vendors are in that Lifecycle.

---

Our last step is back on the Vendor table, just in case you want to see how many vendors are in a given lifecycle back on the Vendors side of things.

For this, back on the Vendor table, you create a new lookup field, in which you’re looking up - within the linked Lifecycles records - the “Count of Records with this Lifecycle” field.

This lookup field is simply saying "hey, i see that there’s a Lifecycle linked here. Let me quickly “dive inside” that linked record and pull out that Count field - which now shows you how many vendors are currently in that Lifecycle.

---

I like to think about linked records as being a “guest star” for the record you’re looking at. On the Vendors table, the the lifecycle records are the “guest stars” and the lookup fields pull info out of those guests.

On the Lifecycle table, the vendors are the guest stars, and they can be looked up, counted, etc.

---

For the % question, you’ll need to build a formula having one field divided by the other field:
{Some Field Numerator}/{Some Other Field Denominator}

Your numerator will be easy enough - it’s simply the Count field from the previous question, because that’s how many vendors have that given Lifecycle.

However, the denominator, the number of records, is a bit tricky, and this short Airtable article explains why.

I recommend taking a look at that, and then you should be able to make your own SUM field.

Then, try making formula field that uses the Count as the numerator and then divide by that SUM you’ll create, something like:

{Count of Records with this Lifecycle}/{SUM}

This may require some more linking around to get it to the table you want it, but it’s definitely doable.

I hope this helps!

[Rachael_Castela]

Thank you so much for the help! I’m having a hard time with summing the entire field.

My issue is that I’m not adding up the Lookup Count (which totals 7), instead I want to add up the Count (in Lifecycle Stage, which totals 5).

Is that possible? I’d like that to be my denominator.

[Jonathan_Katz]

No problem! Here’s what you do:

Create a new Table called “SUM” and just name one record “Vendors Sum”. Nothing else needed. This table is really only going to function so you can add up what you need.

On your “All Accelerator Vendors for Merch” table, create a new Linked Record field, and set it so you’re linking to the SUM table.

Then, still on your Vendors table, link each Vendor record to the first (and only) SUM record in the Sum table, which you’ve called “Vendor Sum”. You’ll have the linked record “Vendors Sum” going straight down on every single Vendor record.

If you head over to your SUM table now, you’ll see that your first (and only) record now has a Linked Record field, and it’s linking to every single vendor on your main vendor table. Again, that’s because the links work both ways.

Now, on the SUM table, create a “Count” field that simply counts everything in that Linked Record field, and boom, you have your sum. Now you can link to that record back on your Vendors table, and use that value in a formula as a denominator.

I can’t make examples at this moment right now, but message again if you need a further hand!

[Rachael_Castela]

Thank you!

I’m looking to show the % of vendors in each lifecycle. As you can see below, the total doesn’t equal 100%. Is that possible? Ideally I’d like to show that 20% of vendors are in PO Placed, 40% are in PO Launched (not 80%, as it shows when I group by PO Launched), etc.

[Jonathan_Katz]

Hey Rachael - this is actually the visually tricky part of showing that % formula field on your Vendors page!

That % formula field is saying “what’s the lifecycle on this record? Show me the % of vendors that have that lifecycle.” It doesn’t care what vendor line it’s on, and it doesn’t care about adding up to 100%. All it wants to do is answer that question.

Since there are two vendors with “PO Launched” as their lifecycle, that % field is going to answer that question twice in row, and it’ll be identical. You might see it a bit better by moving the % formula field directly next to the Lifecycle Stages lookup field.

If that’s visually confusing and you don’t need that information directly on the Vendors table, it could be more straightforward to recreate the % formula field on the Lifecycle table, so each Lifecycle stage record shows you its % coverage.

• Re-create the “Total # of Vendors (from Sum Table)” on the Lifecycle table. This will still be your denominator.
• The numerator will now be the field on the Lifecycle table you’re using to count the linked vendor records on each Lifecycle - it’s the field you looked up in the “Lookup Count” in your screenshot.

In this way, the % in that lifecycle stage will become part of each Lifecycle record, and if you needed to quickly see that information while on the Vendors table, you can just open up one of the linked Lifecycle Stages records and you’ll see it right there.

[Rachael_Castela]

Again, I appreciate your patience!

I’m okay to show the %s on the Lifecycle page.

Forgive my ignorance, but I can’t seem to re-create the total of vendors (from sum table) - do I need to link / associate differently?

[Jonathan_Katz]

Sure! You need to also include the “Link to Sum Table” on the Lifecycle page, because the Total # of Vendors field is actually a lookup of the records in the “Link to Sum Table.”

So, just have each Lifecycle record have a link to “Vendors Sum” and you should be good to go.