Gantt Reworked with ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE

September 5, 2012

Guest post by David Churchward [Twitter]

DAX Studio, ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE

Gantt Hours measure debugging with DAX Studio – isn’t it pretty!

At the end of my last post in the Gantt Chart series, GANTT CHART WITH RESOURCE LOADING, I said that I would return to explain how the Hours measure worked.  I took a brief diversion, almost foray, into the world of CRM PIPELINE FUNNEL CHARTS but I’m now back to explain the workings of that Hours measure.  You can revisit PART1 and PART2 of this series to recap.

In the interim, I’ve been working on some “fine tuning” of that Hours measure.  I’ll come onto the final solution in due course, but I’ll work through the workings of the Hours measure as we left it initially to explain why it needed tuning up!

For those amongst you with an appreciation of SQL, you might think of this like creating a DAX Equivalent LEFT JOIN between tables with a BETWEEN thrown in for good measure.

Read the rest of this entry »


CRM Pipeline Funnel Chart (and something on Themes)

September 3, 2012

Guest post by David Churchward [Twitter]

Pipeline Funnel Chart in Excel PowerPivot

Pipeline Funnel Chart

For those of you waiting on the explanation of the SUMX / SUMMARIZE measure in GANTT POST 2, I have to put you on hold for a while longer.  Sorry.  In truth, I’m working through some performance aspects with that measure.

For now, I thought I would take the opportunity to expose the Pipeline Funnel Chart.  It’s really quite straight forward but it’s incredible how few people know that it’s available to everyone, doesn’t require any real manipulation and YES it’s a standard PowerPivot chart!

For those of you that want to dig straight in, YOU CAN DOWNLOAD THE WORKBOOK HERE

Read the rest of this entry »


Gantt Chart with Resource Loading

August 29, 2012

Guest post by David Churchward [Twitter]

Gantt Chart with Resource Loading Report

Gantt Chart with Resource Loading Report

The response to POST 1 of this Gantt Chart mini series was swift and it didn’t take long for Vegard to contact me with a query regarding resource loading.  Vegard had quickly taken the template and adapted it to include resource hours.  The issue existed with getting the total hours for each resource to behave the way he wanted.  Naturally, individuals will normally work for a set expected amount of time per day and if tasks are planned which exceed that expectation, it’s obvious that they might not get the work done!

In this post, I’ll start to explain how to adapt the model from the previous post to include resource loading and associated subtotals per individual resource.

Read the rest of this entry »


Introducing… the Calendar Chart!

August 9, 2012

 
Calendar Chart in Excel - PowerPivot Can Do Some Amazing Things

“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data:  Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)

New Chart Type Added to Excel 2010!

Yes, it’s a new chart type.  And yes, it’s been added to Excel 2010.  But not by my former colleagues at Microsoft.  This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.

And it’s not some new fancy software addin or something like that.

It’s formulas.  In the normal Excel grid.

Read the rest of this entry »


Creating a Cycle Plot PivotChart

July 31, 2012

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)

Read the rest of this entry »


Dynamic TopN Reports via Slicers, Part 4 – The Dot Plot PivotChart

July 19, 2012

Guest post by Colin Banfield [LinkedIn]

After completing the Part 3 extension of Rob’s Dynamic TopN Reports via Slicers, Part 2 post, I did not plan on a forth installment. However, I did plan to write about creating dot plot PivotCharts sometime in the future. Later, it occurred to me that the TopN reports model provided the perfect foundation upon which to create dot plot charts.

The dot plot is not a standard chart type in Excel, so most users haven’t heard of, let alone used one. The dot plot is an alternative to a horizontal bar chart, and there are many situations where it is a better fit for analysis. For a good introduction to dot plots, see this excellent article by data visualization expert, Naomi Robbins.

The following summarizes some of the potential advantages of the dot plot over a bar chart:

  • When there are a lot of category items on a chart, a bar chart can look cluttered. Because a dot plot uses less “ink” to represent the same data, the resulting chart tends to be less cluttered.
  • The dot plot is often better than a stacked bar chart. The values in a stacked bar chart can be hard to compare because only the bottom bars have a common baseline.
  • Depending on how the dot plot chart is organized, you can gain better insights than using a clustered bar chart (see example in Naomi’s article).
  • Since the absolute length of a bar chart encodes its value, the value axis must start at zero. If the values in the chart are all a distance from zero, you can’t make good use of interval values on the value axis. On the other hand, the dot plot values are judged by position along the axis – length is not involved. Therefore, you can have more optimal intervals on the value axis.

    FIgure 1 shows dot plot PivotCharts based on the data used in Part 3.

    image

    Figure 1 – Dot plot PivotCharts (Click figure for an expanded view)

    Read the rest of this entry »


  • A Brief Note on PowerPivot and Custom PivotCharts

    July 19, 2012

    Guest Post by Colin Banfield [LinkedIn]

    In the next few posts, I plan on demonstrating techniques for creating various types of custom PivotCharts that use PowerPivot data and DAX measures. Each custom chart will have one or more of the following characteristics:

    • Non-native to Excel, but nonetheless useful for various types of analysis. The box-and-whisker plot that I created in this post is one example of a non-native chart.
    • Dynamic in nature, allowing charts to change based on slicer selections. The dynamic charts that I created in this post is one such example.
    • Provide additional context, such as reference lines or conditional formats.

    Why this emphasis on Custom PivotCharts?

    A chart (really a graph, since a chart can be a table or graph. I’m using the term in Excel’s context.) is the best way to analyze data – for detecting patterns, trends, outliers, correlations, making comparisons, and so on.  A custom chart, done correctly, can facilitate the analysis process immensely. There are several Excel sites that are dedicated to the topic of custom charting. However, these sites are primarily focused on building charts from static data in Excel. Excel has not traditionally provided tools for easily interacting with charts created from data in worksheet cells or tables (and the available tools are limited at best). Furthermore, very few posts on the Excel sites discuss charting using PivotTable data. There’s no surprise here – standard Excel PivotTables are exceedingly limited in the way you can organize data for custom charting. For instance, you can’t modify or override row or column filters, and calculated fields are very limited in scope – so much so that I rarely use them.

    However, PowerPivot and DAX ameliorate many of the standard PivotTable limitations – to the point that in many cases, I actually find it easier to organize PivotTable measures for custom charting, compared to organizing data in worksheet ranges or tables. In addition, PivotTables containing DAX measures in combination with appropriate slicers, provide a level of interactivity that is not otherwise possible.

    It’s not all nirvana though – you cannot create scatterplot or bubble PivotCharts. Therefore, an important class of analysis dealing with detecting correlations in data, cannot be done in PivotTables (though such analysis, and more, can be done with the Power View add-in that ships with Excel 2013).

    To date, chart enthusiasts have been severely underserved by the paucity of PowerPivot posts dedicated to the topic of custom charts. Therefore, I will attempt to redress the balance. These custom charts should be relevant for years to come. I was shocked, saddened, and disappointed to learn that no new charts or chart elements were added to the standard chart palette in Excel 2013. Sad smile