Skip to main content

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!

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


First, you’ll need a eCategories] 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 oItems] for now).


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



Back in the eItems] 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.

Reply


Terms & Conditions