Creating a Cycle Plot PivotChart

Guest Post by Colin Banfield [LinkedIn]

A cycle chart is a chart designed to show business sales cycles in a manner that cannot be expressed using other standard charts. For instance, a cycle charts can show how monthly sales vary over several years, or how daily sales vary over several weeks. For a good introduction to cycle charts, see this excellent article by Naomi Robbins.

Figure 1 shows cycle PivotCharts using 3M+ rows of sales data from the Contoso database.

 

image

Figure 1 – Monthly sales over years and daily sales over weeks cycle plots (click figure to see an expanded image)

In figure 1, the red lines represent the average over the period. The slicers in the figure allow the user to hone in on specific periods. You can of course add additional slicers to restrict the data to reflect specific regions, products, etc..

The base measures and row columns that we use for the month over years chart are shown in Figure 2.

image

Figure 2 – Base measures and columns for month over years cycle chart

The base measures and row columns that we use for the day over week chart are shown in Figure 3.

image

Figure 3 – Base measures and columns for day over weeks cycle chart

The formula for AverageSalesOverYears in Figure 2 is:

[AverageSalesOverYears] =

AVERAGEX(ALLSELECTED(CalendarDate[CalendarYear]),[Sum of SalesAmount])

The formula for AverageSalesOverWeeks in Figure 3 is:

[AverageSalesOverWeeks] =

AVERAGEX(ALLSELECTED(CalendarDate[WeekNumberOfYear]),[Sum of SalesAmount])

where CalendarDate is the name of the date table.

Using the data in Figure 2, Excel create the chart shown in Figure 4 (I toned down the gridline display).

image

Figure 4 – Line charts created from data in Figure 2 (Click figure to see an expanded image)

The plot in Figure 4 is completely meaningless. One of my biggest criticisms of PivotCharts is that it has no multidimensional intelligence, even though it’s supposed to chart multidimensional data. It hasn’t a clue that each month-year group is a separate entity, and there’s no option to separate the data on the chart based on hierarchy level. To fix the problem, we must first create duplicate MonthName and DayOfWeekName columns in the CalendarDate table. I’ve called these columns _MonthName and _DayOfWeekName respectively. We then place the duplicate names in the column area of their respective PivotTables. The result of this operation for the month or years cycle chart is shown in Figure 5.

image

Figure 5 – Result is adding duplicate MonthName to column area of PivotTable (Click figure to see an expanded image)

Figure 5 shows that we’ve created independent data series. There is a January series of sales amount and average, a February series of sales amount and average, and so on. Each series contains only the data for a specific month, since only the row and column intersection of the same month has data. Figure 6 shows the PivotChart that results from the data in Figure 5.

image

Figure 6 – PivotChart from data shown in Figure 5 (Click figure to see an expanded image)

The final result shown in Figure 1 is a series formatting exercise.

The month over years chart would probably be more meaningful if more years were plotted. The model is limited because there are only three of data in the Contoso sales table. On the other hand, the day over weeks chart provides considerable choices for filtering weeks.

One Response to Creating a Cycle Plot PivotChart

  1. Scott Kennedy says:

    Colin- Thanks for the post. I am starting to dig into PowerPivot’s and would love to mess around with this workbook. Do you mind sharing a downloadable link?

Leave a Reply