How to Calculate Percentile and Visualize Data Using Dashboard Parameters

To calculate percentiles and use them effectively within a dashboard, follow the steps outlined below. These steps will guide you in creating a dashboard parameter, writing the appropriate query, defining an expression, and setting up the visualization.

Steps to Calculate Percentile and Use It in the Dashboard:

1. Create a Dashboard Parameter:

   • Navigate to the query designer page within your dashboard tool.
   • Create a parameter that will be used in later steps.
     
     

2. Write the Query for Percentile Calculation:
   Use the following SQL query to calculate the 90th percentile of the amount field:

   SELECT
       percentile_cont(0.90) WITHIN GROUP (ORDER BY amount) AS p90_cont
   FROM sales
   

3. Define an Expression Using the Parameter:
   Create an expression that references the dashboard parameter. The following expression can be used:
   

   

   IF([amount] > @{{:Parameter2}}, [amount], 0)
   

   • Here, Parameter2 refers to the dashboard parameter created in Step 1.

4. Use the Expression in the Dashboard Widget:

   • Assign the defined expression to the widget you wish to visualize.
     
     
   • Update the widget’s aggregation setting to none and filter the data with greater than Zero value
     
     
   • The resultant widget data