How to sort Dynamics 365 Sales Pipeline stages in Power BI

Colin Maitland, 05 November 2019

In this article, I demonstrate how to sort Dynamics 365 Sales Pipeline stages in Power BI.

The following image shows part of an analysis of Open Opportunities by Sales Pipeline stage. The Sales Pipeline stages have been implemented using Business Process Flow stages; e.g. Qualify, Develop, Propose and Close.

The Business Process Flow dimension in the Power BI data model consists of several merged Dynamics 365 Opportunity related Business Process Flow entities. To ensure that the Sales Pipeline stages are sorted correctly, I have added a hidden custom Sort Order column to the Business Process Flow dimension using a Power Query Add Conditional Column step and subsequent Transform Data Type step. The Transform Data Type step transforms the Output values from the Add Condition Column step from Text to Whole Number. The following image shows the configuration of the Add Conditional Column step. This step assigns a Sort Order value to each Business Process Flow stage.

This column is then used to configure the Sort By Column for the Name (Active Stage) column in the Business Process Flow dimension. The Sort By Column option is located on the Modelling tab of the Power BI Desktop ribbon bar.

Because the calculated Sort Order is dependent on Name (Active Stage) column, it is not possible to use a calculated column as the Sort By Column.

The following error is subsequently displayed when attempting to configure such a calculated Sort Order column to be the Sort By Column for the Name (Active Stage) column.

This configuration based on using the Custom Conditional Column added using Power Query results in the sort order for the Name (Active Stage) column being set to Qualify, Develop, Propose and Close. This sort order is used by default by charts, tables, and matrixes in Power BI.

In conclusion, I usually prefer to use Calculated Columns to generate Sort Order columns, but if there is a dependency, such as that demonstrated in this article, I use Power Query to add a Custom Conditional Column.