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.
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.
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.
Create a manual virtual table with the following SQL statement:
SELECT 'Region A' [Region], 'Department A' [Department Name], 200 [Budget], 500 [Expense]
SELECT 'Region A' [Region], 'Department B' [Department Name], 500 [Budget], 500 [Expense]
SELECT 'Region B' [Region], 'Department A' [Department Name], 3000 [Budget], 200 [Expense]
SELECT 'Region B' [Region], 'Department B' [Department Name], 1000 [Budget], 1500 [Expense]
SELECT 'Region C' [Region], 'Department A' [Department Name], 1000 [Budget], 1500 [Expense]
SELECT 'Region C' [Region], 'Department B' [Department Name], 500 [Budget], 1500 [Expense]
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.
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.
The final result should look like this: