Limit Donut Graph to Defined Percentage

Introduction

The donut graph was recently introduced to our Dashboards and uses a PowerQuery to aggregate the data that it displays. 

Our Engineering team updated the donut chart in Aug. 2021 to enable the number of slices to be configured. As a result, the largest slices are displayed as individual segments of the donut chart, while the remaining segments are consolidated within the "Other" category (where X is the "Maximum Slices" configured).

Although the graph configuration is the recommended solution, I have left the PowerQuery that was previously suggested as an example.

Recommended Solution

Simply configure the "Maximum Slices" field in the "Edit Panel" dialog of the donut chart:

Old Solution - PowerQuery

I originally created the following PowerQuery to limit the segments displayed on a donut graph to top percentages only. Items which fall below the defined percentage are consolidated in the "Other" category. This streamlines the donut graph's results and speeds up the rendering process. I have left this as an example of the versatility of PowerQueries.

This example graphs the log volumes of the top 80% of files on your platform

| union (
tag='logVolume' metric='logBytes' !(k8s-cluster=*) forlogfile!="none"
| group c=sum(value) by forlogfile, host
| sort -c
| let d=running_percent(c)
| let h=(d<=80.0 ? 0 : c)
| let i=overall_sum(h)
| limit 1
| let logfile="Other"
| columns logfile, total_bytes=i),
( tag='logVolume' metric='logBytes' !(k8s-cluster=*) forlogfile!="none"
| group c=sum(value) by forlogfile, host
| sort -c
| let d=running_percent(c)
| filter d<=80.0
| columns logfile=forlogfile, total_bytes=c)

Notes

• The query used for donut graphs is relatively straightforward. Its result is a list of items with a sum (or count) associated with each row. The sum for all items is totaled, and the amount associated with each row is the percentage.
• In the first query of our union statement, we perform a group by aggregation and sort the rows in descending order of their sum (c).
  • We then calculate the running percentage of each row (d), and output the sum (c) in row (h) if the running percentage of the row is greater than 80%.
  • We get the total (i) of sums for rows that are greater than 80% cutoff -- this is the basis of our "Other" category
  • Since we have calculated the total of sums needed for other (i), we rename the columns so that only one row is returned.
• The second query of the union statement lists all rows in descending order of their sum. The columns are then renamed so that they match our naming convention in the first query
• The first query is used to calculate the "Other" category from the lower 20% of results, while the second query displays the top 80%. The union statement joins the data from both queries into a single table.