Welcome to my latest blog post, where I will dive into the dynamic world of data visualization and analytics using a powerful tool: Microsoft Power BI Choropleth Maps.
If you've ever wondered how to unlock the potential of your Business Central item sales data and gain actionable insights at a state-level granularity, you're in the right place. In this article, I will guide you through the process of harnessing the capabilities of Power BI Choropleth Maps to analyze and present your item sales data like never before. Prepare to embark on a journey that will enable you to transform raw numbers into compelling visual narratives, uncover regional trends, and make informed decisions that drive your business forward.
Whether you're a data enthusiast, an analyst, or a business owner, get ready to unlock the geographical dimension of your data and unleash the power of visualization with us. Let's chart a course to a deeper understanding of your Business Central item sales, one state at a time.
For this blog we will create a Choropleth map by customer sales. The color of each state will change based upon the total customer sales for the state within a date range. States with darker colors indicate higher sales than states with lighter colors. In the example above the states of Nevada, Kansas, and West Virginia have the highest sales for item number 1920-S between the dates of January 1st, 2022 and October 1st, 2022.
We will use two Power BI slicer visuals on the map. The first slicer will let the user adjust the date range under analysis. The second slicer will let the user select the item(s) under analysis. Let's get going.
1. First you need to have an Azure Maps Account and have the feature turned on in Power BI by your tenant administrator.
2. We need to turn off the Auto date/time feature under the Options / Current File / Data Load section.
3. Let's get the data for this solution. In the lower menu click on Get data and get the Business Central customers and itemLedgerEntries tables under the Standard APIs v2.0 folder.
4. We need to create a Measure that will filter item ledger entries by sourceType and sum them. Click on customers in the Data pane. Then in the lower menu click on New measure and then enter the following:
itemSales = CALCULATE(SUM(itemLedgerEntries[salesAmountActual]),itemLedgerEntries[sourceType]="Customer")5. Click on the state field under the customer table in the Data pane. In the lower menu change the Data category property to State or Province.
6. Next, we will create a date table with a custom hierarchy. Click on Modeling in the upper menu and then New table in the lower menu. Then enter the following:
calendar = CALENDAR(FIRSTDATE(itemLedgerEntries[postingDate]),LASTDATE(itemLedgerEntries[postingDate]))7. We will now create the hierarchy component in the date table. Click on the calendar table in the Data pane.
a. Click on New column in the lower menu and enter:
Month = MONTH('calendar'[Date])
b. Click on New column in the lower menu and enter:
Year = YEAR('calendar'[Date])
c. Right mouse on the Year column under the calendar table and click on Create hierarchy.
d. Right click on the Month column and then click on Add to hierarchy. Select Year Hierarchy.
e. Right mouse on Year Hierarchy and rename the column to Calendar Hierarchy
8. We now want to create relationships between the tables. Create the following two relationships after clicking on the Model view on the left of the screen.
a. calendar (Date) to itemLedgerEntries (postingDate)
b. customer (number) to itemLedgerEntries (sourceNumber)
9. Click on the state field under the customer table in the Data pane. The map visual will show on the Page and the state field will now appear in the Location property under Visualizations.
10. Drag and drop the itemSales field under the customer table in the Data pane to the Tooltips property in the Visualizations pane. This will provide the user with the state's sales when they hover over the state.
11. Click on the Filled map visual under the Visualizations pane.
12. In the Visualizations pane click on the Format your visual button. Then click on the conditional formatting button.
13. In the pop-up make the following changes and then click the OK button:
a. Select Gradient for the Format style.
b. Select the itemSales field in the customer table for the basis.
c. Optional: Change the Minimum and/or Maximum color. I like purple.
14. Click on the Page and then click on the Slicer visual under Visualizations. Click on the Date field under the calendar table.
15. Click on the Page and then click on the Slicer visual under Visualizations pane one more time. Click on the Date field under the calendar table. Click on the itemNumber field under the itemLedgerEntries table. Then click on the Format your visual tab and set the Select all switch to On under the Selection section.
16. In the itemNumber slicer click on the Select all option so you can view all the items.
17. Change the date range and item numbers and you will see changes in the colors on your map.
As we wrap up our exploration of using Microsoft Power BI Choropleth Maps for in-depth analysis of Business Central item sales by state, it's evident that the fusion of data and visualization opens new avenues of understanding. With each map, we've unearthed valuable patterns and insights that inform strategic decisions. Remember, the geographical perspective provides a context that numbers alone cannot convey. Armed with this knowledge, you're poised to elevate your business strategies, target regions with precision, and optimize your operations. So, harness the power of data visualization, keep experimenting, and let the maps guide you toward continued success in your business endeavors.