Does anyone knows how to create Pareto plot on databricks lakeview dashboard?

[Paddy_chu]

Hi,

Does anyone knows how to create Pareto plot on databricks lakeview dashboard? My stakeholder would really like to see this display on the dashboard. 

I'm able to get the values on a bar chart and create a cumulative percentage variable, but when I'm using combo display, I'm not able to sort the bars in descending order, for some reason it just sorted by alphabetic order. Here are the variables I'm using:

x-axis = account names

y-axis (bar) = potential score

y-axis (line) = cumulative percentage of potential score.

Would appreciate if anyone could provide possible solution or any alternative ideas. 

Thanks,

Patrick

[BigRoux]

The sorting limitation in combo charts appears to be an intentional design constraint in some visualization implementations. In combo charts, sorting by the y-axis is commonly disabled to avoid issues with multiple metrics where sorting logic could create ambiguity. For example, if a bar chart and a line chart are displayed together, automatic sorting might conflict with the line chart rendering.

Workaround 1: Pre-Sort Your Data in SQL

A straightforward way to enforce the correct sorting (by descending potential score) is to pre-sort your data in your SQL query. This ensures that the data fed into the visualization is already in the desired order. Here's what you'd do:

1. Modify your SQL query:

   SELECT 
       account_name,
       potential_score,
       ROUND(SUM(potential_score) OVER (ORDER BY potential_score DESC) * 100 / SUM(SUM(potential_score)) OVER (), 2) AS cumulative_percentage
   FROM 
       your_table_name
   ORDER BY
       potential_score DESC;

   SQL
   • Replace your_table_name with your actual table name.
   • This query pre-sorts by potential_score in descending order and computes the cumulative percentage directly.
   • Feeding this pre-sorted data into the combo visualization will ensure the bars are displayed in the correct order.

2. Workaround 2: Use a Different Visualization Approach

   If the alphabetical sorting persists for combo charts or you need finer-grained control:

   1. Separate the visualizations:

      • Create a standard bar chart for account_name vs. potential_score and ensure it is sorted in descending order by preparing the data as outlined in "Workaround 1".
      • Separately, create a line chart for cumulative_percentage with the same x-axis values.
        1

   2. Combine with external tools:

      • If this sorting behavior cannot be resolved directly in Lakeview Dashboards, consider creating the combined chart in an external BI tool like Tableau or Power BI and embed it within your Databricks dashboard.

[Paddy_chu]

Thank you for your reply, I tried the pre-sorting on workaround 1, but when I display that on combo plot it automatically sort my x-axis by alphabetic order. It would be perfect if workaround 1 works, this seems to be the simplest solution. 

I've been attempting to use Power BI, as suggested in workaround 2, to create a plot and embed it into my Databricks dashboard. Do you have any resources or links that explain how to do this? My company settings prevent me from using Power BI to run data on the Databricks cluster, but I'm interested in trying it because Databricks' dashboard visualizations seem limited.

Thanks!

[BigRoux]

Currently, it is not possible to directly embed a Power BI visualization into a Lakeview (AI/BI) dashboard in Databricks. The embedding functionality in Databricks Lakeview dashboards supports adding visualizations, text, and other widgets specifically built within the Databricks ecosystem. Power BI visualizations cannot be integrated directly into these dashboards.

[Paddy_chu]

Thank you for your reply. Just trying to understand more to your point on this:

Combine with external tools:

• If this sorting behavior cannot be resolved directly in Lakeview Dashboards, consider creating the combined chart in an external BI tool like Tableau or Power BI and embed it within your Databricks dashboard.

Would you have any example to how to create the combined chart in external BI tool and embed in Databricks dashboard if PowerBI won't work?

[BigRoux]

I do not have any handy.  A bit of research on your part might get you what you need.  Also, the recommendations to embed in Lakeview refers to embedding a static image of your chart in Lakeview dashboards.  You cannot integrate third party tools in Lakeview.  Hope this helps. Louis