topic Re: How to Display Top Categories in Databricks AI/BI Dashboard? in Warehousing & Analytics

How to Display Top Categories in Databricks AI/BI Dashboard?

Akshay_Petkar — Mon, 14 Oct 2024 11:58:57 GMT

In a Databricks AI/BI dashboard, I have a field with multiple categories (e.g., district-wise sales with 50 districts). How can I display only the top few categories (like the top 10) based on a specific metric such as sales?

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

Mo — Wed, 16 Oct 2024 14:10:52 GMT

hello @Akshay_Petkar,

in your data tab, can't you create a dataset using "Create From SQL" and write a query like the following and use this dataset in your canvas?

SELECT COUNT(sales_key) total_sales, distirict FROM my_table

GROUP BY distirict

ORDER BY total_sales DESC

LIMIT 10

let me know if I get your question wrong 😉

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

Akshay_Petkar — Thu, 17 Oct 2024 03:26:16 GMT

Hello @Mo

I am creating a AI/BI dashboard where I need to add multiple charts, each showing the top 10 values. For example, I used a query like select * from table and in the canvas, I added a chart with 50 unique values, but I want to display only the top 10. In another chart, there are 100 unique values, and again, I need to show the top 10 only. How can I apply this limit across all the charts?

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

migq2 — Thu, 21 Nov 2024 22:36:05 GMT

I was having the same question and creating a new SQL dataset for this breaks cross filtering. Since the data is coming from different datasets cross filtering does not work nicely

Is there a more elegant solution to this?

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

Mo — Tue, 10 Dec 2024 09:02:26 GMT

hey @migq2 , @maks

in the AI/BI dashboards in your data, add a limit parameter like:
select all from my_table limit :limit_number

to all your tables.

when you're on canvas and adding visualizations, add a filter and create a parameter with single value:

Then you can use that parameter to limit the numbers you're displaying for all the visuals that have that parameter in the limit. here I used the tables in samples.tpch and limit the numbers to 3 rows:

and this a sample query on orders table:

SELECT * FROM samples.tpch.orders limit :limit_number

let me know if this helps or you have doubts.

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

Akshay_Petkar — Tue, 11 Feb 2025 03:39:12 GMT

Hi @Mo ,

The parameter limits the rows from the dataset that we read in the query. For example, We create a city-wise sales chart at that time, it aggregates the values and performs a group by. This parameter will not perform on that. It will limit only the rows from the dataset

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

erbg — Wed, 16 Apr 2025 09:43:34 GMT

Did you find a solution?

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

GunaR — Mon, 18 Aug 2025 09:20:24 GMT

i got a similar challenge of displaying top 10 rows based on a metrics?

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

BS_THE_ANALYST — Mon, 18 Aug 2025 11:51:10 GMT

@GunaR is this not where we'd want to leverage a parameter? And then pair this with a filter on the dashboard? Perhaps you create an aggregate SQL query for the various metrics

If you can't use a parameter in the ORDER BY clause, which you likely can't because you want a column name to to be output from the parameter, you could use something like this:

I think the CASE statement is a good workaround as a parameter will output a string field. The CASE statement then points it back to the column reference.

Make the parameter a choice and then the user will be able to select the appropriate metric to ORDER BY? In a LIMIT clause, you then just specify a numeric parameter with the appropriate number so they can limit top N

All the best,
BS

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

GunaR — Tue, 19 Aug 2025 05:17:24 GMT

Thank you for the prompt response.

Good that Parameter helps to make the sort dynamic, In my case i got a common dataset which get used across all the pages in the dashboard. Only few widgets need to limit the top 20 records ordered by certain column, rest all should return all the rows.

Re: How to Display Top Categories in Databricks AI/BI Dashboard?

GunaR — Fri, 21 Nov 2025 05:06:36 GMT

This feature is released under the Oct-2025 round-up. Refer the Topic: "Dashboards Top/Bottom N Bar Chart Categories"

https://www.databricks.com/blog/whats-new-aibi-october-2025-roundup