Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Small Multiple Charts


    Small multiple charts is a term popularised by Edward Tufte. They allow comparing data series as they are repeated for different members.

    In XLCubed they are useful for visualising large amounts of data, as hundreds of data points can be plotted and compared easily.

    This allows users to find trends and outliers in the data more easily than by trying to study a large table of data.

    Video demonstration

    For a tutorial, try watching our Small Multiple Videos


    As mentioned previously it can be difficult to understand outliers, trends etc when the data is presented as just numbers and text as can be seen below. Using Small Multiples with this example below will guide you through creating meaningful information from the data.

    Task Pane: building a set of Charts Example Basic

    Before             After

    As you can see the image on the right is a much easier way of viewing the data, compared to the grid.

    Let's work through an example.

    Category defines the Category (X Axis) within each individual Chart, in this case a Trend over Time

    Before             After

    The Series defines the number of data series to be charted.

    In order to be able to visualise a simple trend this would be easier with Lines rather than Columns. Use the Chart Type on the Options panel of the Task Pane to change the chart style.

    Before             After

    By dragging the Region hierarchy onto columns, and then selecting Children of All, we can split data into separate charts at Region level.

    We can additionally split this by Channel by dragging the Channel hierarchy onto rows,

    Now we have a graph for each Region by Sales Channel. The Scales (Y axis) are shared so that the charts can be easily compared.

    More detail is available for each chart by double clicking on the individual chart eg Direct Sales / Australia Pacific, where the data is unclear because of low numbers can be further explored like this. The scale is automatically adjusted to give a clearer picture of the data. Use the back button to return to the higher level.

    Before             After

    Manipulating the chart to show Each Product by Channel, simply drag the Products from the Series to the Rows Below you can clearly see the effect of swapping the Product (Rows) and the Channel (Series)

    Before             After

    Additionally you can drive the data selection by using Slicers or Excel ranges.

    Here we have defined a Slicer on Time at Year level and used it to drive the Categories.

    Small Multiple Chart Designer

    Insert a Small Multiples chart by using the Small Multiples option on the ribbon, or the Insert -> Small Multiples Menu option.


    After inserting a Small Multiples Chart the above windows will be displayed. The Small Multiples Charts - Task Pane is automatically displayed on the right of the sheet. The Task Pane can also be accessed from the right mouse button pop-up menu.

    To delete the Small Multiple chart use the right mouse button pop-up menu -> Delete option.

    To Move, Size or Close the Task Pane, drag it using the window bar or use the drop down menu on the Task Pane Header.


    The data within the Charts can be refreshed (like a grid) by using the right mouse button pop-up menu -> Refresh option

    There is also an initial Warning displayed on the empty chart as it is essential for the design of a Small Multiple Chart set that a Category is selected. This warning can also be seen on the Task Pane.

    Display Options

    Using some of the other features in the Options section of the Task Panel, charts can be made to display data in order to be able to better view correlations and or outliers


    Define the number of Columns Displayed

    When selecting the X Axis Children of the Geography we receive the following message, because there more columns returned than the Max Number of Columns defined on the Properties for the Chart. i.e there are 6 values, for Children of Geography.




    Alter the Max Charts on Columns to ‘6’ to display all the Geography – Children.

    Note the Warning indicator icon in the bottom left corner of the Chart has now disappeared as all the data is displayed.


    As there is nothing selected on the Rows, a better way to display this information, given its long narrow nature would be to turn on Chart wrapping.


    The data still has a common scale and is more easily compared.


    Move the Legend

    Using Right Mouse Menu select the position for the Legend, Right, Bottom or None. If None is selected the Legend can easily be retrieved by using the Right Mouse button anywhere on the Chart.


    Highlight a Series

    Using the Legend series on the Charts can be highlighted, this is useful where series overlap. Here the Reseller Order Count appears brighter. Multiple series can be selected by using Cntl Click.


    Drill Into: for a breakdown on the Column

    Before             After

    Using the Right Mouse Menu Drill Into the data for Canada, you can continue drilling as far as the Hierarchy allows.

    Using the Back button returns to the previous level of Charts

    From Version 8.1 you can limit the level to which users can drill. This can be especially useful for hierarchies which have many members at a low level when charting all of these doesn't give a usable chart.


    Keep Only: for a closer look at a single Column

    Before             After

    Click on the chart you wish to keep and use the Keep Only menu option to remove the other charts

    Keep Except: for a closer Comparison

    Before             After

    Using this option you can get a closer comparison between two or more Geographies. Here we have removed the United States and Canada, in order to get a better look at how the other Geographies compare to each other. Notice the scales have been adjusted to reflect only the data displayed.

    Relationships between Measures

    Colour Coding

    Colour Coding can be applied to the data points in order to highlight differences. Colour Coding can be applied using the plotted Measure, or another selected Measure. Tick the Colour box in the Options area of the Task Pane; this makes the colour gradient active.

    Before             After

    A warning will appear that No Values Hierarchy chosen next to the Values box in the Options area. Drag the hierachy to plot, normally the Measures to the Values box and select the Measure to be plotted ( Y Axis)

    Next select the value to apply the gradient to. This can either be the same Measure that is being charted or another measure altogether.

    Before             After

    On the left, the Reseller Sales Amount is plotted and Chart coloured based on the Reseller Order Quantity.

    On the right, the Reseller Sales Amount is plotted and Chart coloured based on the Discount Amount The colours used for charting can be changed by using the button to the right of the colour swatch.


    Here by looking at the Sales Amount, coloured by different values we can see that although there were large volumes of Sales in the USA they were also the most heavily discounted.


    Plotting a second Series, using a second Y Axis

    It would be useful to view the Order Numbers vs. the Reseller Sales Amounts; if they are both plotted on the same scale it is almost impossible to see the Order Count values as they are so small by comparison. Move to other axis allows you to see two Y Axis scales, one each for Amount and Count.

    Using the Right Mouse Menu, whilst positioned on the Legend Entry for the series you wish to move Click Move to other Axis


    Scatter Charts: plotting a correlation

    Plotting a correlation, using a scatter chart option - firstly define the Y axis and then the X axis against which it is to be plotted. Here we can see how the Sales Amount and Order Count are related - closely in Australia, but spread out in the United States.

    Before             After

    In a second example, for selected Geographies, we can apply colour to the chart using a third measure, Discount Amount. Additionally the data is split by Product Category on the rows.


    Heavily discounted sales can be seen as red data points.

    Using the first example the chart type can be changed to a Bubble. Using the Discount Amount to determine the size of the bubble it can be seen that the greatest discounts occur in the United States.

    Before             After

    Drilling through the individual charts, using the Right Mouse can then provide more details as to where the actual sales occur. eg. in Australia, most Sales with high Discounts occur in New South Wales in the Lane Cove area.

    Before             After

    Use the back button to navigate back through the previous charts.

    Headers: Filtering the Data

    By moving the Measures to the Headers you can add to and or change the currently charted measure.

    By moving any of the other Hierarchies into the Headers area filters can be applied to the data. eg. Here we are looking at Reseller Sales for just Accessories and Clothing Sales.

    Before             After

    Versus Sales for Components below: Components are sold in more locations than the Clothing and Accessories. Note that these selections can be driven from an Excel range or Slicer and could therefore either be preset or selected by the viewer.


    The Small Multiple Chart Properties can be accessed either from the right mouse button pop-up menu -> Properties or from the tool bar at the top of the Small Multiple Charts Task Pane



    Colour Palette: Select the Colour Palette for the Chart

    Colour by Category: Make each category have its own colour

    Space chart areas: When ticked creates a space between each chart:

    After             After

    Max charts on Columns: the maximum number of Charts displayed in each Column.

    Max charts on Rows: the maximum number of Charts displayed in each Row.

    Note: if the total number of charts exceeds the Number of Rows times the Number of Columns, then some charts will not be displayed. To display them increase either the number or Rows Columns or both.

    When Wrapping charts:

    • Auto Arrange: XLCubed overrides the Column and Row Counts to give a balanced view of the data.

    Note: The Column and Row counts still apply as maximums, so if there are not enough spaces the warning message will appear, i.e. if there are 3 columns and 1 row only 3 charts will be displayed, and a warning message given if there are more that number of charts, however the charts will be displayed as a 2 by 2 block.

    • Fix Column Count: Uses the Column count first to determine the number of Columns displayed, then display as many charts as possible given the row count.

    Apply Cube formatting: Uses the measure's number format to format labels

    Missing values: Defines how the null source values are handled

    • Plot missing values as zero: Plots the null values as if they were zeroes
    • Don't plot missing values: Leaves a space where the null values are
    • Interpolate missing values: Creates a value between neighbouring values to plot

    Line chart line width: From Version 7.5, the thickness of the lines for line charts.

    Show Tooltip From Version 7.5, the tooltip to display when hovering over a point in the chart. Placeholders may be used to put data into the tooltip text:

    • %value%: The numeric vertical value at the point
    • %valuex%: The numeric x (horizontal) value at the point, if applicable
    • %category%: The category member at the point
    • %series%: The series the point belongs to
    • %column%: The column member at the point
    • %row%: The row member at the point
    • %color%: The numeric value that controls the colour of the point, if applicable
    • %size%: The numeric value that controls the size of the point, if applicable

    Numeric values can be formatted using Custom number formats.


    Chart Name the Name of the Chart for easy reference

    Output Cells From Version 7.5, XLCubed places the selected member for the selected point into the chosen cell. This value can then be used to drive other charts, formulae and/or Grid selections.

    In this example, the Column member Geography – France is placed in $A$26, and Product Categories – [1] Bikes in $A$27. This value can then be used to drive other charts, formulas and or grid selections:


    Link To From Version 7.5, when a point on the chart is clicked, this range is selected.

    Clear cells where no selection made From Version 8.1, the cell is cleared if no row/column is selected

    • Unique Name: Displays the unique identifier for the selected hierarchy in the selected cell
    • Caption: Displays a label of caption associated with the hierarchy in the selected cell

    Placement Controls the behaviour of the chart when surrounding cells are moved and resized:

    • Free floating: The chart ignores all cell size changes
    • Move: The chart is moved together with the surrounding cells, but is not resized by them
    • Move and Size: The chart is moved and resized together with the surrounding cells

    Show Back/Forward controls Controls whether back and forward controls are displayed on the chart to undo and redo changes made to it

    Enable high performance charts If ticked, high performance charts are used

    Axes Controls the minima and maxima of the vertical axes

    • Note that it isn't possible to set the minimum to a value between -1 and 0 or 0 and 1, the chart will round to 1. If you need a percentage smaller than 1 then you can create a measure of Value * 100 and set the format string of the axis to 0.00"%".

    Titles and Axes

    From Version 7.5

    Chart titles: The font used to display the column and row members

    Chart axes:

    • Label font The font used to display the labels on the axis
    • Label angle The angle to show the label text at
    • Allow label truncation Allows label text to be shortened in order to display tidily
    • Stagger labels Allows alternate labels to be shifted in order to improve readability
    • End label visible Shows or hides the last label
    • Primary axis number format The number format to use for the primary vertical axis
    • Secondary axis number format The number format to use for the secondary vertical axis

    From Version 8.1

    • Automatic Title Allows entering the axis titles for the x and y labels
    • Category Labels: Determines the display of the Category Labels on the Chart.
    • Automatic: XLCubed will decide how many labels to display, based on the space available.
    • Every Category: each category will be displayed regardless of the space available.
    • None: No labels will be displayed.
    • Custom: The labels will be shown based on a number you enter.
    After             After

    Legend: The font used to display the legend items

    Colours and Style

    From Version 7.6

    Chart background: the colour used to fill in the area behind the charts

    Chart area background: the colour used to fill the individual charts

    Axis colour: the colour used to draw the vertical and horizontal axes

    Gridline colour: the colour used to draw any gridlines on each chart

    Show columns and bars as cylinders: when ticked, column and bar charts are filled with a 3D effect

    Marker style: for chart types that use them (e.g. scatter charts), the shape of the markers:

    SMC Triangle.png Triangle
    SMC Star10.png Star10
    SMC Star6.png Star6
    SMC Star5.png Star5
    SMC Star4.png Star4
    SMC Square.png Square
    SMC Diamond.png Diamond
    SMC Cross.png Cross
    SMC Circle.png Circle

    Marker size: for chart types that use them (e.g. scatter charts, dot charts), the size of the markers



    Top Padding: Sets the top padding of the Small Multiples chart

    Bottom Padding: Sets the bottom padding of the Small Multiples chart

    Title Height: Sets the height of the chart title

    XAxis Text Height: Sets the height of the xAxis text

    YAxis Text Width: Sets the width of the yAxis text

    Y2Axis Text Width: Sets the width of the y2Axis text

    Legend Size: Sets the size of the legend (From Version 8.1)


    From Version 8.1

    Limit drilling to: Limit the chart to drill down to a specified level only

    Colour and Gradient Picker


    The colour gradient picker is used to determine both the colours and the rate at which the colour changes.This can have a dramatic effect on the impact on the data being displayed and is a valuable tool for making sure that data becomes information

    After             After

    Here the only change was made to the centre of the colour bar

    Chart type

    Use the drop-down on chart-type as below to see the many different chart types available within XLCubed for use within Small Multiples graphing.


    Bump charts

    These are new in Version 8. Bump Charts plot the rank position rather than the actual value. For example if I sell a product in a marketplace with 10 other competitors I may like to see how the rank position of my product and the competition changes over time to see if we are gaining or losing market position. You must have more than one series so that the rankings can be compared. Usually you will want dates on the category axis so the trends are shown across time.


    Bump charts are only available on small multiples based on cube data.


    New in Version 7.5 - you can right-click on a Series (or the chart for all series) to add a trendline:


    You can then select the type of trendline and formatting in the Trendline Options

    Interactive Charting

    New in Version 7.5 - charts can be made an interactive, clickable part of the overall report


    You can customise the look of a series by right clicking it in the Legend and selecting "Edit Series..."

    From Version 9 you can specify the series colour in the format sheet.

    To do this you set the "Item Name" for either the name of the Chart (specified in the options form) or SmallMultiples to apply to all small multiples.

    The Hierarchy and Member should be specified, and the colour will be taken from the Data Format cell in the row.

    Using Percentages

    The minimum value for an axis must be a whole number, which can be an issue when using percentages as you may want the scale to go from 0.5 (50%) to 0.9 (90%) for example.

    You can do this by plotting the numbers multiplied by 100, and then setting the number format of the axis to display the % sign after the number using the format 0"%".

    This means the scale starts at a while number and the axis labelling is also correct.

    If you need more precision you can also use a format like 0.00"%".

    As the axis is scaled by 100 you must also scale the values being plotted. If you are creating a small multiple against a cube the easiest way to do this is to create a Custom Calculation with the expression [Measures].[My Measure] * 100 and plot that.


    Small Multiple Charts based on other data sources

    From XLCubed v7.5, you may also insert Small Multiple Charts based on data from sources other than Analysis Services. See the appropriate pages for more information:

    Switch Chart Type

    New in v8.1 Small Multiples is the ability to change the chart type by series.

    The option is available by right-clicking on the Legend > Chart Type:


    Reference Lines

    New in v8.1 Small Multiples is the ability to draw a reference line behind your data.

    You can have one or two lines, and optionally shade the area in between them.

    The reference values come from an Excel range. You get a line for each row of data, and the shape of the line is determined from the values in the columns.


    Reference lines are not available for Bar charts.

    Row customisation

    New in Version 9.1 is the ability to customise a row of charts so they are displayed differently.

    For example you might have a row of charts for different measures. In this case you might want a different y-axis scale for each row, and a different chart type. Before making any changes the "Discount Amount" is not easy to see as it shares the same scale as the "Reseller Sales Amount".


    If we go into the chart Properties -> Axis Scales tab, we can change "Y Axis Common Scale" from "All Charts" to "Row of Charts". Then if we right click a chart on the bottom row we get options to set "Row Chart Type" and "Edit row..." which allow further customisation of the display. The result is a chart that is much more easily read to compare the trends.


    Note this option only applies to charts with a selection on Rows, this feature is not triggered by wrapping charts.

    Data Table

    Also new in Version 9.1 is the ability to show a data table of the numbers behind the chart, and to copy these into Excel for further analysis if required. Simply select "View Data" from the right click menu to pop up the numbers. You can click on a chart area to get the data for that individual chart, or on the category labels to get the data for all charts at once.


    See Also