Breakout Value

This article refers to the latest XLCubed version. For other versions, see Breakout Value/Versions.

The Breakout Value option is available on the right mouse button pop-up menu when the currently active cell contains an XL3Lookup function, a XL3ValueRankLookup function, or a Grid data cell.

The Breakout Value option allows an interesting piece of data to be further explored and broken down into component parts. For example, if the cell showing Reseller Sales Amount for the United States region in of 2003 is broken out by product, then the result will be the top 10 (or however many items are selected to Breakout by) products for the United States region in 2003. The items broken out can then themselves be broken out by another hierarchy to analyse more deeply.

1. Select a cell to analyse
2. Select Break Out from the right mouse button pop-up menu
3. The Breakout dialog appears:

1. The Position to insert breakout is used to specify the starting cell on the spread sheet where the Breakout values are to be inserted
2. The Hierarchy to breakout field is used to select a dimension to use to Break out the cell value
3. Show top or bottom members and Breakout count determines the number of rows of Breakout information that are generated, and whether they should be of the highest or lowest ranked members
4. The How to breakout hierarchy field (when Breaking out from a Grid), or the Rank by selection field (when Breaking out a formula) is used to choose a selection from the Breakout hierarchy
5. Show percentages include a % column to show that items contribution to the parent value
6. Track active cell see section below
7. Display percentage bar include a incell bar to represent the % value
8. Include 'Others' grouping If breaking out to display the top 5 members - this option will create an "Others" grouping to group all the items outside of the top 5
9. Exclude zeros exclude members with a 0 value from the breakout
10. Exclude nulls exclude members with no values from the breakout
4. The result is inserted into the sheet.

5. An item in the Breakout can then be further broken out.

Track Active Cell

With "Active Cell Tracking" enabled then clicking on another data value in the source grid will update the breakout values to reflect that selection. The "Active Cell" is highlighted to show the current selection.

Breakout of "United Kingdom", "All Periods"

Breakout of "United States", "CY 2001"

See Also

• Breakout and Propagate – Oldies but Goodies on the XLCubed Blog