Posts Tagged Dynamics 365

Power BI and mixed Team/User Ownership in Dynamics 365

In Dynamics 365 it is easy to assign cases, opportunities, etc. to a Team. This is a very convenient feature and when working with the built-in charts and dashboards, all the charts nicely puts either the team or user name on the legend, depending on what type the owner happens to be.

However, Power BI doesn’t play so nice right off the bat. The reason for that is that the owner field in Dynamics 365 can point to either the User table or the Team table. That means you will need to let Power BI know which table to look at for each record.

As always, there are many ways this challenge can be resolved, but I would like to highlight a few ways that I’ve been working with.

The most important part of this post is simply to recognize that Power BI behaves in this way, and act accordingly. Otherwise, you may end up with weird looking visualizations, or worse, a wrong understanding of what your data really looks like.

I’ll use the Case entity as an example as it is commonly assigned to both users and teams.

Add Type of Ownership and Owner to table

The most simple approach is to add two extra fields on the Case table. One to indicate if ownership type is “User” or “Team”. The other with the name of the user or the team.

The system name for the Case table is of course “incidents”, so that is what will be used throughout these formulas. The examples assume no changes has been made to the column or table names in Power BI from when they were imported from Dynamics 365.

Create a new column and enter this formula or copy and paste from below the image.

Power BI for Dynamics 365 Dynamics CRM and new column to define Owner Type for team and user ownership in Dynamics 365 Dynamics CRM

OwnerType = IF(incidents[_owningteam_value]="","User","Team")

It the field for the owning team is empty, the value will be set to “User”, if it has a value, it will be “Team”. This defines the ownership type.

Create another new column and enter this formula. It is similar to the formula just used, but it inserts the name from the user table or team table instead.

Owner = 
        IF(incidents[_owningteam_value]="",
        LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_owninguser_value]),
        LOOKUPVALUE(teams[name],teams[teamid],incidents[_owningteam_value]))

When entering this formula in Power BI, it would normally be on one line. I added line breaks so it is easier to read and to copy and paste from.

Now, the two new columns can be used in visualizations so you can easily drill down on the specific type of ownership.

Adding two columns in Power BI to better handle mixed ownership between users and teams in Dynamics 365 Dynamics CRM

Click to enlarge animation adding both columns.

Create an Owner Table

Rather than bringing the data in to the Case table, we can create one “Owner” table with all the users and teams in it, along with their unique ID in one column. This table can then be used for all the entities that have both user and team ownership. This method can also be used if you have more attributes on the ownership that you want to categorize by. For example, the manager of the user or the administrator of the team, can all be brought in to this table.

To create this one table with ownership details, I will need to append the User and Team tables.

Click “Edit Queries” to open the “Query Editor”

Edit Queries in Power BI

Click on Append Queries and choose”Append Queries as New”
Append Queries as New in Power BI

The difference between “Append Queries” and “Append Queries as New”, is that “Append Queries as New” will create an additional table with our data that we can work with separately. “Append Queries” will bring data in to an existing table. I am choosing the “as New” option so I still have the user and team table to work with separately if needed.

In the dialog, select the two tables to append and click ok.

Append Queries as New select tables dialog in Power BI - Dynamics 365

Wait for tables to merge and rename the table from “Append1” to “owner”.

Close and Apply the changes.

Close and Apply Changes in Query Editor in Power BI

Find the new Owner table and add two columns indicating the ownership type and the name of the user or team. Similar to the first method described, except simpler since all the values you need are now in the same table.

One new column with the type to indicate if it is either a user or a team

Type = IF(owner[teamid]="","User","Team")

 

One new column with the name of the user or team.

Name = IF(owner[teamname]="",owner[fullname],owner[teamname])

 

One new column which contains the GUID of the user or team.

_ownerid_value = IF(owner[teamid]="",owner[systemuserid],owner[teamid])

I called this column “_ownerid_value”. Not the easiest to type, but it is the same as the schemaname for the mixed user/team owner field. Giving it this name will make it much easier for the autodetect relationships feature to give us some good results.

At the end you should have these three new columns in your owner table.

New columns added in Power BI with Ownership Type, Name of user or team and the GUID.

Finally, go to Relationships and add the relationship between cases and the new owner table matching _ownerid_value to _ownerid_value. Quickest way to do that is to drag _ownerid_value from incidents to the corresponding field on the owner table.

Power BI relationship editor - drag and drop owner id from one table to the other.

Alternatively, click Manage Relationships and run the autodetect feature.

Power BI Manage Relationships and use Autodetect feature.

 

Go back to reports and use the new owner table in some of your visualizations and verify that it is working.

Use new appended owner table with Dynamics 365 Users and Owners in Power BI visulizations.

One consideration for this approach is that you double the amount of data in Power BI consumed by the user and team table. However, these are generally the smallest tables in a Dynamics 365 environments, so the impact is likely limited. I would be cautious of using a similar approach to handle the Customer field that can point to either an Account or Contact.

Other considerations

With the DAX formulas used in Power BI, there are always options to make it more advanced. In my first example, I imported both the user ID and the Team ID into the cases table and based my logic in the DAX formula off of those. I could have imported only the one ownerid and have had the DAX formula base its logic on the content of the user and team tables, rather than the extra columns within the same table. That will save a few columns when importing data and you’ll have fewer columns to navigate.

The DAX formula that brings in the Team or User name, with the logic based on data from other tables would look like this.

Owner = IF(ISTEXT
    (LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value])),
    LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value]),
    LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_ownerid_value]))

 

Thanks for reading, and as always, please follow me on Twitter  and sign up for my email newsletter to get the latest tips and tricks to get the most out of your Dynamics 365 data.

, , ,

Leave a comment

Time to expand the scope of the CRM Chart Guy blog

Dynamics 365 has now arrived, and as any other update to the Dynamics CRM/365 product for the past five years, it pretty much contained zero updates to the built-in charting engine. The good news there is that all of my blog posts from the past five years are still valid and I won’t have to update any of them. That, of course, is also the bad news. I believe there are a few areas where Microsoft could have enabled more features on the charts. Features like properly formatting keywords and adding these to annotations. Something the .NET charts are capable of, but for mysterious reasons, are not enabled in Dynamics 365. Enabling this would, for example, allow you to add a nicely formatted total value to a funnel or pie chart.

It is clear, that the direction Microsoft is taking for analytics in Dynamics 365, is to take the data out of Dynamics 365, create visualizations and KPIs elsewhere, and then display the results back inside Dynamics 365. This has been the case with Power BI, and it is also the case with the new Customer Insights and Relationship Analytics features.

The best example of that, which is available now, is the Connected Field Services solution that allows you to embed a record specific Power BI tile inside a Dynamics 365 form, proving a much tighter integration than what we have seen previously.

Power BI tile, record specific in Dynamics 365 form.

Power BI Tile in Dynamics 365

Selector for Power BI tile in Dynamics 365 form

Selection options for Power BI Tile in Dynamics form.

Coming soon is the Relationship Analytics solution, powered by Azure Customer Insights, which also contains visualization and KPIs that are record-specific and is another example of where analytics and charting in Dynamics 365 are headed.

relationship-analytics

Relationship Analytics, powered by Azure Customer Insights

I have recently written a longer piece detailing these different solutions including a comparison matrix. You can find that on the Elev8 Solutions blog here.

While I have very much enjoyed writing a blog focused exclusively on getting the most out of the built-in analytical features in Dynamics, it is time to expand the horizon. Power BI is no longer an external reporting tool and there are many exciting new features coming with Customer Insights. All intended to be an integral part of the Dynamics 365 offering so I will start to include these new tools in my blog posts.

In other words, Dynamics 365, and getting the most out of the data in there, will still be the primary focus for this blog, but the toolset is changing.

This does not mean that I will stop writing about the built-in charts and the XML. This area still has some distinct advantages and I certainly intend to keep my “Ultimate Sales Dashboard” project open as long as people are downloading it.

Hope everyone else is as excited for the new features, and the new scope for my blog as I am.

As always, thanks for reading and please follow me on Twitter  and sign up for my newsletter.

Best,
Ulrik B. Carlsson (CRMChartGuy)

, , ,

Leave a comment

Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.

Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.

Crowdsourcing?

I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.

Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.

Suggestions could include, but are certainly not limited to:

  • Ideas for new KPIs
  • Ideas for new Charts
  • Ideas to improve formatting and make the charts easier to decode
  • Using charts contextually on record forms
  • Better use of colors between the different charts

My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.

At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.

Assumptions

It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:

  • Only use the charts within Dynamics 365/CRM*
  • Only use system fields
  • Only use system relationships
  • Avoid using calculated fields for KPIs – unless it is a system field
  • Take as much advantage of the existing setup as possible

*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!

 

The Sales Dashboard

Main sales dashboard with the most important KPIs for Dynamics 365/CRM

The Sales Dashboard consists of the following charts:

1. Sales Funnel

Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.

2. Sales Leaderboard

The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.

3. Sales KPIs about the open opportunities

KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.

4. Phase Loss Rate on Est. Revenue

This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.

Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.

5. Sales KPIs for Won Opportunities

Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.

Competitor Strength

Scroll down on the dashboard and you get to the Competitor Strength chart.

Competitor Strength for Open Opportunities in Dynamics 365/CRM

The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.

On top of the lost bar, you can see how many opportunities are currently in progress.

Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.

Sales Pipeline Analysis

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.

Download

As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

Update Notes November 20, 2016.

Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.

Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.

Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.

Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.

Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.

Original Solutions Notes

All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already. Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc.

The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.

The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.

Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.

All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.

Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.

And don’t forget to sign up for my newsletter on this page and follow me on Twitter

, , , , , , , ,

12 Comments