To go along with our Dynamics 365 Sales Data Model, we now have our first open source Power BI Report available on Github. The Report is a “My Sales” experience for Dynamics 365.
To get people to voluntarily update their Opportunities and Activities, it’s important to maximize the value they get out of CRM. Power BI can play a key role – it’s the best way to summarize CRM data points from multiple tables in a single view.
Summary
Free Power BI Content
You can download the files here. This open source content is all covered under the MIT License terms (“go nuts”).
End User Reports
One of the key parts to company-wide CRM reporting is to get users to update data. That’s why we’ve focused first on an experience for them. Providing salespeople value first gets the data flywheel moving.
“My Data” Calculations
These reports have an interesting concept – users can filter on “My Data” or they can view All Data without Record Level Security. This also makes it simpler for a Power BI Admin to preview the reports by manually selecting different users.
No Leaderboards
Why do Sales reports look like a 1980’s game show? If you want a leaderboard medal 🥇, you can buy or make one.
Giving Value to Get Value
Data related processes aren’t always designed to add value for the user tasked with doing them. Example processes might be Salespeople updating statuses and probability, consultants entering timesheets and task estimates, and Microsoft prompting us all with those “How likely are you to recommend…” surveys.
To get users to volunteer information, an effective report or process needs to provide 10x the value the user puts in. If updating CRM takes 5 minutes, a user needs to receive back 50 minutes of value. If it’s not an order of magnitude net time savings, users will take shortcuts.
It is challenging to design a report that makes a salesperson more productive through analytics, without knowing the specific process of the company and sales team. I focused on managing activities – showing when the last activity update was entered in CRM and showing open activities. I thought this could show at a glance Opportunities and Accounts that are languishing and might need follow up.
Even if this report isn’t perfect, writing imperfect reports increases the quality of the underlying Data Model.
Power BI vs CRM Dashboards
CRM Dashboards are better than Power BI in a couple of ways:
- They show real-time values.
- They are better at filtering subsets of data, then immediately allowing the user to open and manage that data.
Although Power BI can show real-time data, its true strength is storing calculation-heavy, transformed data and surfacing interesting insights.
For example, I transformed the Probability Integer into ranges that are easier to understand and summarize. This analysis can make it easier to review flaws; for example, how can something be 91-100% probable but also have a “Cold” rating.
Another example in this data model is with Activities. When filtering on an Account, you can view all the Activities linked to the Account AND linked to related Opportunities.
Filtering My Data Versus Record Level Security
There are Power BI scenarios where a user wants to see their data by default, but they need the option to see all data. Record Level Security in Power BI doesn’t solve this. (If it did, it would be named Record Level Suggestion.)
The other typical design is to give a user a drop-down of all Salespeople. The user could select their own name and save a Bookmark to quickly retrieve their data.
This data model and report has a third path. It has a concept of a “My Data” filter that uses special calculated measures to do the filtering. If the filter is unselected, the user sees All Data. This is a great DAX concept for pre-filtering data.
Below is one of the calculated measures that uses USERPRINCIPALNAME() in the DAX expression:
Count of My Opportunities =
VAR FilterMyData =
IF (
SELECTEDVALUE ( 'My Data Filter'[My Data Filter] ) = "My Data",
TRUE (),
FALSE ()
)
VAR FilterUsers =
FILTER ( Users, Users[Email] = USERPRINCIPALNAME () )
RETURN
IF (
FilterMyData,
CALCULATE ( [Count of Opportunities], FilterUsers ),
[Count of Opportunities]
)Salespeople Have Higher Emotional Intelligence Than Template Reporting Suggests
Good reporting helps a person take action. I’m not sure what action a Leaderboard helps a Salesperson or Sales Manager take.
We have to be careful as report designers in introducing our own bias into the design. A bias of “salespeople only care about making money” will lead towards very specific types of reports. The reports may reinforce those stereotypes!
Joel Leichty 