# Rank Pivots by a Measure (Top N)

**URL:** <https://community.omni.co/t/rank-pivots-by-a-measure-top-n/225>\
**Category:** Modeling\
**Tags:** modeling, pivot, visualization\
**Created:** [February 1, 2025, 8:46pm UTC](https://community.omni.co/t/rank-pivots-by-a-measure-top-n/225 "2025-02-01T20:46:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![russ](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/russ/32/296_2.png) [@russ](https://community.omni.co/u/russ)\
**Post date:** [February 1, 2025, 8:46pm UTC](https://community.omni.co/t/rank-pivots-by-a-measure-top-n/225/1 "2025-02-01T20:46:59Z")

</div>

**NOTE:** Omni has now released Top N functionality natively in the workbook. \* **How do I get it?** In the workbook’s field browser, right click on a field and select the **Top N** option.  
There are still some instances where the pattern below is needed, such as a rank that must listen to all outside filters

If you have a chart where you’d like to show the Top 3, 5, 15 or any number of values based on how they rank over a measure - this tutorial will show you how in a few short steps.

**Turn this:**

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/9d09493b86585990bc7ca78a3b0e9ace2a9fe0a2.png)  
 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/d275f4ff835919c29d442dd8adbae7f9c8495584.png)

**Into this:**

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/c7fca27fa085b8827ab0f73c9f339f1f376e8402.png)  
 ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/d21c0e54475bf92176ffdfd7510ae523c7793859.png)

> **Text based walkthrough with code**
>
> ## Step 1 ) Create a Query ranking your dimension over your measure
> 
> ![image](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/1f3740979bc49c1f875a22bb79d3b2456c45c0a5.png)
> 
> ## Step 2) Turn it into a Query View in omni
> 
> ![Screenshot 2025-02-01 at 12.21.49 PM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/d7459c9c621c7c2d78d19739cf2aca1bf7b5d581.png)
> 
> This will create a new view in your workbook model. You can look at it my going to **Model \> Model layers \> Workbook** and then you should find a file by the name you selected that looks something like this:
> 
> ```yaml
> # Reference this view as example_rank_dimension
> schema: PUBLIC
> query:
> fields:
> account.billing_state: billing_state
> calc_1: rank
> opportunity.total_arr: total_arr
> base_view: account
> calculations:
> calc_1:
> sql: OMNI_FX_RANK(${opportunity.total_arr},
> OMNI_OFFSET(${opportunity.total_arr}, -536870911, 0, 1073741823, 1))
> sorts:
> - field: opportunity.total_arr
> desc: true
> topic: account
> # add this parameter to accept all topic filters into the inner CTE where the rank will be calculated
> bind_all_filters: true
> 
> dimensions:
> rank: {}
> total_arr: {}
> 
> billing_state:
> primary_key: true
> 
> measures:
> count:
> aggregate_type: count
> 
> ```
> 
> ## Step 3) Join it back into the topic
> 
> ```yaml
> base_view: account
> 
> joins:
> sfdcuser: {}
> opportunity: {}
> example_rank_dimension: {} #our query view
> 
> relationships:
> - join_from_view: account
> join_to_view: example_rank_dimension
> join_type: always_left
> # join on the dimension you are ranking
> on_sql: ${account.billing_state} = ${example_rank_dimension.billing_state} 
> relationship_type: many_to_one
> 
> ```
> 
> ## Step 4) Add an ‘other’ bucket, and filter field to put the long tail into a single grouping
> 
> Back in the view file:
> 
> ```yaml
> 
> dimensions:
> ....
> # prepend our rank to the dimension for better alphanumeric sorting
> ranked_state:
> sql: ${example_rank_dimension.rank} || ') ' || ${example_rank_dimension.billing_state}
> hidden: true
> 
> # we apply our filter only field here, CASE/WHEN'ing the rank into the other bucket 
> # with Omni Templated filters
> ranked_state_with_other_bucket:
> sql: |
> CASE 
> WHEN {{# example_rank_dimension.other_threshold.filter }}${example_rank_dimension.rank}{{/ example_rank_dimension.other_threshold.filter }} 
> THEN ${example_rank_dimension.ranked_state} 
> ELSE 'x) Other' 
> END
> label: Billing State (Ranked by ARR)
> 
> ......
> # this 'filter only field' will allow us to place a condition on what ranks are individually shown
> filters:
> other_threshold:
> type: number
> label: Billing State Rank Threshold for 'Other Bucket'
> description: Allows you to set criteria for when a rank will be shown, vs placed
> 
> ```
> 
> ## Step 5) Optional – cleanup the topic to make more intuitive
> 
> We might want to make the ranked dimension appear like it’s all part of the same view. To do that we’ll hide some fields and apply view\_labels and group\_labels to our filter and add some special logic to control the number of padded zeros to ensure alphanumeric sorting is always correct.
> 
> Here’s the whole adjusted view file:
> 
> ```yaml
> # Reference this view as example_rank_dimension
> schema: PUBLIC
> query:
> fields:
> account.billing_state: billing_state
> opportunity.total_arr: total_arr
> calc_1: rank
> base_view: account
> calculations:
> calc_1:
> sql: OMNI_FX_RANK(${opportunity.total_arr},
> OMNI_OFFSET(${opportunity.total_arr}, -536870911, 0, 1073741823, 1))
> sorts:
> - field: opportunity.total_arr
> desc: true
> topic: account
> bind_all_filters: true
> 
> dimensions:
> total_arr:
> hidden: true
> 
> rank:
> description: Allows you to completely filter out states based on their ARR rank
> group_label: Billing State
> view_label: Account
> 
> pad:
> sql: |
> REPEAT('0', LENGTH(CAST({{ filters.example_rank_dimension.other_threshold.value }} AS VARCHAR))-1) 
> hidden: true
> 
> padded_rank:
> sql: |
> SUBSTRING(${example_rank_dimension.pad}, 1 * LENGTH(CAST(${example_rank_dimension.rank} AS VARCHAR))) || ${example_rank_dimension.rank}
> hidden: true
> 
> ranked_state:
> sql: ${example_rank_dimension.padded_rank} || ') ' || ${example_rank_dimension.billing_state}
> hidden: true
> 
> billing_state:
> hidden: true
> primary_key: true
> 
> ranked_state_with_other_bucket:
> sql: |
> CASE 
> WHEN {{# example_rank_dimension.other_threshold.filter }}${example_rank_dimension.rank}{{/ example_rank_dimension.other_threshold.filter }} 
> THEN ${example_rank_dimension.ranked_state} 
> ELSE 'x) Other' 
> END
> label: Billing State (Ranked by ARR)
> group_label: Billing State
> view_label: Account
> 
> filters:
> other_threshold:
> type: number
> label: Billing State Rank Threshold for 'Other Bucket'
> group_label: Billing State
> description: Allows you to set criteria for when a rank will be shown, vs placed
> into an Other Bucket
> view_label: Account
> 
> ```

> **Video Walkthrough**
>
> ### Part 1 – The Issue
> 
> https://www.loom.com/embed/44903ab1428d4361ba140eeb790f8f6a?sid=5b48ec6c-2c1f-46c8-a5ea-72b4ad019dbe
> 
> ### Part 2 – What the pattern does, and getting the query view
> 
> https://www.loom.com/embed/130fb731ea7945aa8851ac065e9a5d90?sid=7524b231-a80e-4c45-9bef-9fa2428a7f78
> 
> ### Part 3 – Joining Back into our topic
> 
> https://www.loom.com/embed/844d75d64c974f5c8df1aede0e3e329e?sid=3dbb0c0c-4501-4d9f-bec5-3d22d1f477a6
> 
> ### Part 4 – Explaining the view logic
> 
> https://www.loom.com/embed/9d69fd07656f4f2fa3180bfa536818c0?sid=db4b03e1-bf51-4f4b-898a-7f24b519a68b
> 
> ### Part 5 – Final Cleanups
> 
> https://www.loom.com/embed/70af6e8819f54389b434ceb791ef90fb?sid=6d482db8-eb6d-45a8-9c15-53c60a45ffd9

---

<div class="post-metadata">

**Author:** ![kyjoro](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@kyjoro](https://community.omni.co/u/kyjoro)\
**Post date:** [December 18, 2025, 4:39pm UTC](https://community.omni.co/t/rank-pivots-by-a-measure-top-n/225/2 "2025-12-18T16:39:39Z")

</div>

We now have a native Top N feature! Check it out in the field picker and docs will be coming soon!

 ![Screenshot 2025-12-18 at 8.39.16 AM](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/3ad1ae8c246d51cd7bf12fecfab995863bce1b64.png)