Relative Formulas

[Camille_Sharrow]

Hi! I’m hoping to create a ranking relative to the category an item is in and can’t figure out the best way.

My dataset includes items in multiple categories, ranked 1-1000 overall based on sales. I also have a “Rank in Category” field that ranks the items in each category, and category totals are anywhere from 37 to 242. Is there an easy way to make a fractional calculation relative to each category?

For example, item 4 overall is actually 1 for its category of 92 items. The output can be 1/92 as a decimal, I’m creating a scoring system from outputs and that will help normalize it.

Thanks!

[Justin_Barrett]

Welcome to the community, @Camille_Sharrow-Blau! :grinning_face_with_big_eyes: Sorry that nobody has responded to this yet. :frowning: Here are my thoughts on this setup.

First, you’ll need a [Categories] table to contain all of your item categories, and will need to link to the proper category for each item from your main table (I’ll call this [Items] for now).

After you’ve linked all items to their respective categories, add a field named {Count} to the [Categories] table, and make it a count of the linked records from [Items]. Here’s how that looks in my quick test:

Back in the [Items] table, add a lookup field that brings in this count value. I named mine {Category Count}. Here’s how that looks so far:

You already have the {Rank in Category} field, so all that’s needed is a formula that takes the rank and divides it by the {Category Count} value, formatting it as a decimal:

{Rank in Category} / {Category Count}

Here’s the final result:

Not an ideal example with so few items in each category, but it should work better with your actual data.