Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# Relative Formulas

Topic Labels: Formulas
1057 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

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!

18 - Pluto

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.