Tracking and Totalling Data in a Pivot Diagram

data graphics in a pivot diagram[br]In the final tutorial of the Visualizing Data series we look at how adding data graphics to your Pivot Diagram can help businesses highlight the strengths and weaknesses within their operation.

[br]Using the Add Totals panel in the Pivot Table task pane we add extra calculations to the shapes in our Sales Pipeline diagram to give more detailed information about how they have performed.

Totalling Data in a Pivot Diagram

In this tutorial we look at how easy it is to add an additional field to the tree structure of our pivot diagram. The revenue field is added to the pivot diagram and the SUM function used to total it, but the total Visio adds is formatted to 2 decimal places and this makes the shapes look cluttered.

TIP: Changing the formatting for a calculated field in a pivot diagram is simple; by opening the edit text dialogue box the value can be altered.

[br]Shapes are most useful when they accurately show the data in relation to other shapes, and this is easiest to see when the data is laid out neatly. Where Visio automatically calculates totals, averages or any other kind of calculation it does not always present this data in the best way so a bit of human intervesntion is often needed when totalling fields in a Pivot Diagram.

Changing the formatting for a calculated field in a pivot diagram is simple; you can alter the formatting by opening the Edit Text dialogue box. In the example shown we reduce the number of decimal places to 0 and, as Visio has put the minus sign for negative values on its own line within some shapes on the pivot diagram, we make sure that any negatives have a minus sign that appears on the same line as the number. This is a great way of ensuring that the data displayed in each shape fits neatly inside the available space and makes the pivot diagram easier to read.

Adding Data Graphics to our Pivot Diagram

Next we add colour to the data graphics in order to visually represent the degree of variance from the target for each holiday type. An extra level is added to give greater diversity to the range of colours and enable a better graphical representation of the data in the pivot daigram. This makes it more relevant to the data being displayed, and as it changes, so the pivot diagram changes to reflect them.

totalling data in a pivot diagram