Microsoft Power BI: Custom Bar Colors Helps you Show Your Dynamics 365 CRM Data Better

Building a visualization that shows performance to goal is a common practice in Microsoft Power BI. In this article, we are going to dive a little deeper into visualizations and how color helps to emphasize data in your CRM system including Microsoft Dynamics 365 for Sales.

One visualization that is commonly used in Dynamics 365 and Power BI is the “Line and Clustered Column Chart”. See the sample below where the line typically shows the goal and the bars show the actual metric for the given period. An excellent way to draw quick attention to the periods that have achieved the goal is to dynamically change the color of the bar when the goal is met or exceeded.

Below is an example of a visualization that shows monthly Sales Amount (bars) and the Sales Goal (dashed line). Note how each time the value of the Sales Amount exceeds the value of the Sales Goal—the color is set to green. When the Sales Amount falls short of that Sales Goal — the bar color is set to tan.

DELTA to GOAL VALUE

The key to building this visual is to create a Delta to Goal value. This can be done by adding a calculated column in Power BI or creating a measure. In this example,  the Delta to Goal field was already added.

Microsoft Power BI can easily achieve the desired visualization by simply applying these simple steps.

• Add a Line Chart and cluster column chart to the canvas.
• Place the Date in the Shared axis field, the Sales Amount in the Column values field, and the Sales Goal in the Line value

• With visualization still selected, click on the Format icon (paint roller) in the visualization pane.
• Open the Data colors section of the Visualization pane and set the Sales Goal color to Gray and the Sales Amount color to Green. NOTE: it is important to set the Sales Goal to the color that you want to use to show “meeting or exceeding the goal”. If you do not set the color before applying conditional formatting, the default color (usually blue) will appear next to the Sales Amount Label in the Legend.
• After setting the Sales Amount color to green, click on the fx button under the Default color This will open the Default Color – Data colors screen.
• Change the Based on Field to Sum of Delta to Goal. This value is used to determine if the Sales Amount is greater than the Sales Goal. The color of the bar will be determined by whether the delta value is greater than or less than 0.

• Select Custom from the Minimum drop-down, enter 0 in the field below Custom and choose the color Tan from the color drop-down (in this example, a custom color D9CBA0 was used for tan).
• Do the same for the Maximum drop-down, select Custom, enter 0 and choose the color Green. Setting the minimum and maximum values to 0 will cause any value below 0 to display the minimum color and any value greater than or equal to 0 to display the maximum color.
• Click OK to save.

I hope this simple example will help your performance to goal visualizations really stand out. Microsoft Dynamics 365 Business Central and Power BI have a lot of good tools and automated presets. As a data master, you need to show data quickly and effectively. Being able to decipher data makes you a key contributor to your organization. For help with this and other tips for the Microsoft Power Platform (Power BI, Power Apps, Power Automate, and Power Virtual Agent), contact JourneyTEAM today.

Article by: Jim Hill – Senior BI & Analytics Consultant | 801-436-6636

JourneyTEAM is an award-winning consulting firm with proven technology and measurable results. They take Microsoft products; Dynamics 365, SharePoint intranet, Office 365, Azure, CRM, GP, NAV, SL, AX, and modify them to work for you. The team has expert level, Microsoft Gold certified consultants that dive deep into the dynamics of your organization and solve complex issues. They have solutions for sales, marketing, productivity, collaboration, analytics, accounting, security and more. www.journeyteam.com