416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Sorting stacked bar chart by total value

Hide navigation
RSS
Modified on Thu, 12 Jun 2014 09:58 AM Categorized as Chart, Data Preparation, KPIs, Virtual Tables

Overview

This article shows how to sort a stacked bar chart by its total value.

The chart receives the data first by series (after the data is split into multiple series), then by the data points. Therefore, the stacked bar chart can be sorted by sorting the measure value on the KPI. This will place the highest datapoint first in the stack which means that the corresponding bar will be placed first on the chart. The rest of the bars follow this pattern and the series corresponding to the highest datapoint will be placed first in all of them. The following image shows the result of the sorting. The data table is shown below the chart along with a data grid to show how the sorting occurs and gets displayed on the chart.

Result

Result


The steps on how to create this along with the sample data is given below.

Note that if the first series is missing some category that only exists in later series, it's going to be placed last there is currently no way for the chart to know how to reliably piece together the original order given only separate, incomplete lists of text-based categories. Either the first series of the chart will somehow need to contain all of the categories so that their order can be preserved, or a Standard Dimension (full dimension) can be used with a sortable column used as the key value, so that the chart -> X Axes -> Scale -> Sort Order can be set to Ascending.

Steps

The steps shown here is to show how the sorting takes place on the chart using the sample data. If the data already exists along with the virtual table and the KPI, then proceed directly to the sorting stage of the KPI.

Virtual Table

Create a manual virtual table with the following SQL statement:

SELECT 'Region A' Region, 'Department A' Department Name, 200 Budget, 500 Expense UNION ALL SELECT 'Region A' Region, 'Department B' Department Name, 500 Budget, 500 Expense UNION ALL SELECT 'Region B' Region, 'Department A' Department Name, 3000 Budget, 200 Expense UNION ALL SELECT 'Region B' Region, 'Department B' Department Name, 1000 Budget, 1500 Expense UNION ALL SELECT 'Region C' Region, 'Department A' Department Name, 1000 Budget, 1500 Expense UNION ALL SELECT 'Region C' Region, 'Department B' Department Name, 500 Budget, 1500 Expense

Virtual Table

Virtual Table


KPI

Create a KPI from the virtual table with the following settings:

  • Measure: Budget
  • Axis: Region
  • Splitter: Department Name

Under the Ordering and Sorting tab of the KPI, add a sorting rule that sorts the measure Budget in an ascending or descending order depending on the requirements. In this example the sorting is done in a descending order.

Sorting Rule

Sorting Rule


KPI Preview

KPI Preview


Stacked Bar Chart

From the toolbox, drag and drop a stacked bar chart control on the canvas and drop the sorted KPI on it. For an understanding on how the sorting is taking place display the data table by setting the X-Axis-->Labels-->Display Data Table property of the chart to True.

If needed, also drop a data grid control on the canvas and drop the KPI onto it. The group descriptions are created by default and the sort order is displayed. The order of the groups is the order of the series on the chart and the order of the individual rows in the groups is the order in which the stacked bars are formed.

Result

The final result should look like this:

Result

Result


Related Topics

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.