cancel
Showing results for 
Search instead for 
Did you mean: 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Akshay_Petkar
Contributor III

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?

5 REPLIES 5

Mo
Databricks Employee
Databricks Employee

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 😉

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?

migq2
New Contributor III

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?

Mo
Databricks Employee
Databricks Employee

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:

Mo_0-1733821128971.png

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:

Mo_1-1733821245900.png

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.

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

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now