Occasional rantings about Dynamics CRM/365, Power BI, SharePoint, Office 365 and Azure cloud. Intrigued about how people collaborate and data driven decision making. Taking the first small steps in machine learning. Putting all of the above in practical use to help companies "embrace" their customers

Monday, August 17, 2015

Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 1

A couple of weeks ago I wrote a posting about Combining Dynamics CRM Online and Power BI Preview but since then a lot of exciting things have been released and announced. One of these things is the fact that Power BI Designer has been rebranded to Power BI Desktop and a lot of new functionality has been added.. Power BI Desktop basically ties together Power Query, Power Pivot and Power View in a standalone application, removing the constraint of having to use Excel 2013 to design visualizations but it also extends the existing functionality quite significantly. The first thing that you need to do when you want to build reports is to get at the data – Microsoft Power BI Desktop support a huge number of data sources but the one I’m interested in is Dynamics CRM Online.

You will notice that the actual values are not being displayed – this is because both CustomerId and EstimatedValue contain complex values, which you can expand by clicking the expand icon in the column header.

Since we only need aggregated data (not the individual opportunities) for the different top customers, we are going to group the data by CustomerId and sum the estimated revenue.

Afterwards you can limit the data by only retrieving the top 20 rows.

Finally we need to visualize the data on a report. In editing mode, we will drop a bar chart control on the designer surface and define the data elements which needs to be displayed

Power BI Desktop also has a wide variety of display options that you can configure for your visualization such as the different options for X and Y axis (show labels, start and end values), colors to use for the data labels, which display unit to use for the data labels(including precision). One thing that I’m still missing though is the option to show the actual values instead of using display units for values below 1.000.