How to Create Awesome Business Central Data Analysis

In my last post, 3 Thing to Know About Dynamics 365 Business Central, one of the things I discussed was how to connect Power BI to Dynamics 365 Business Central.  The Power BI Content Pack has several reports. Two of those are “Item Sales Dashboard” and “Customer Sales Dashboard” (screenshots below). These are a good start, but enhance our data analysis by connecting to our data and transforming it and adding some DAX measures.

 

 

Creating our Visuals

First some basics before we connect to our Dynamics 365 Business Central data.  We need a Power BI model with a good date dimension table and there is no better place to start then with #sqlbi.com DAX Date template. Alberto and Marco do a great job teaching all things DAX, so explore around while you’re on their web site.

Now let’s add Dynamics 365 Business Central as a data source in Power BI Desktop

1. In Power BI Desktop, in the left navigation pane, choose Get Data.
2. In the Get Datawindow, choose Online Services, choose Microsoft Dynamics 365 Business Central, and then choose the Connect button.
3. Power BI displays a wizard that will guide you through the connection process. You will be prompted to sign into the service. Select Sign in and choose the account you would like to sign in as. This should be the same account you sign into Dynamics 365 Business Central with. 
4. Choose the Connect button to continue. The Power BI wizard shows a list of Microsoft Business Central companies and data sources. These data sources represent all the web services that you have published from each company in Microsoft Dynamics 365 Business Central.
5. Alternatively, create a new web service URL in Dynamics 365 Business Central by using the Create Data Set action in the Web Servicespage, using the Set Up Reporting Assisted Setup guide, or by choosing the Edit in Excel action in any lists.
6. Specify the data you want to add to your data model, and then choose the Load
7. Repeat the previous steps to add additional Microsoft Dynamics 365 Business Central, or other data, to your Power BI data model.

Once the data is loaded it will appear in the right navigation on the page. At this point, you have successfully connected to your Microsoft Dynamics 365 Business Central data and are ready to begin building your Power BI report.

Before building your report, we recommend that you import the Microsoft Dynamics 365 Business Central theme file. The theme file will create a color palette so that you can build reports with the same color styling as the Microsoft Dynamics 365 Business Central content packs without requiring you to define custom colors for each visual.

 

Here are the data tables we are going to import:

• InventoryItems
• InventoryTransactions
• ItemLedgerEntries
• ItemSalesandProfits

After importing the InventoryItems data we need to make the following changes.

• Rename the “No” column to “Item No”
• Remove several columns listed below in the M Code.
• Add a custom column to calculate Unit Profit

Here is the M code to help you with the transformations.  You will need to change the source information to match your environment.

let

Source = Dynamics365BusinessCentral.Contents(null),

#”CRONUS USA, Inc.” = Source{[Name=”CRONUS USA, Inc.”]}[Data],

InventoryItems_table = #”CRONUS USA, Inc.”{[Name=”InventoryItems”,Signature=”table”]}[Data],

#”Renamed Columns” = Table.RenameColumns(InventoryItems_table,{{“No”, “Item No”}}),

#”Trimmed Text” = Table.TransformColumns(#”Renamed Columns”,{{“Description”, Text.Trim, type text}}),

#”Removed Columns” = Table.RemoveColumns(#”Trimmed Text”,{“Created_From_Nonstock_Item”, “Substitutes_Exist”, “Stockkeeping_Unit_Exists”, “Assembly_BOM”, “Production_BOM_No”, “Routing_No”, “Shelf_No”, “Cost_is_Adjusted”, “Standard_Cost”, “Last_Direct_Cost”, “Price_Profit_Calculation”, “VAT_Prod_Posting_Group”, “Item_Disc_Group”, “Tariff_No”, “Search_Description”, “Overhead_Rate”, “Indirect_Cost_Percent”, “Blocked”, “Last_Date_Modified”, “Manufacturing_Policy”, “Flushing_Method”, “Assembly_Policy”, “Default_Deferral_Template_Code”, “Global_Dimension_1_Filter”, “Global_Dimension_2_Filter”, “Location_Filter”, “Drop_Shipment_Filter”, “Variant_Filter”, “Lot_No_Filter”, “Serial_No_Filter”, “ETag”, “Item_Tracking_Code”}),

#”Added Custom” = Table.AddColumn(#”Removed Columns”, “Unit Profit”, each [Unit_Price]-[Unit_Cost]),

#”Replaced Value” = Table.ReplaceValue(#”Added Custom”,””,”KIT”,Replacer.ReplaceValue,{“Item_Category_Code”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Replaced Value”,{{“Gen_Prod_Posting_Group”, “Channel Name”}, {“Item_Category_Code”, “Item Category”}, {“Base_Unit_of_Measure”, “Unit of Measure”}, {“Profit_Percent”, “Profit Percent”}, {“Inventory”, “Inventory Qty”}, {“Unit_Cost”, “Unit Cost”}, {“Unit_Price”, “Unit Price”}})

in

#”Renamed Columns1″

Now let’s duplicate the Inventory Transactions data and make some changes to it.

1. Right click on the InventoryTransactions table and select “Duplicate”
2. Rename the duplicated table to Inventory Amounts
3. Remove several columns. They are listed below in the M code
4. Select the Transform tab and then the Group By icon on the menu
5. Select the advanced option and group the data by Posting_Date and Item_No and sum the Inventory Quantity

 

Here is the M code to help you with the transformations.  You will need to change the source information to match your environment.

let

Source = Dynamics365BusinessCentral.Contents(null),

#”CRONUS USA, Inc.” = Source{[Name=”CRONUS USA, Inc.”]}[Data],

InventoryTransactions_table = #”CRONUS USA, Inc.”{[Name=”InventoryTransactions”,Signature=”table”]}[Data],

#”Renamed Columns” = Table.RenameColumns(InventoryTransactions_table,{{“Document_No”, “Document No”}}),

#”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“Expiration_Date”, “ETag”, “Document_Type”, “Document No”, “Document_Line_No”, “Variant_Code”, “Description”, “Return_Reason_Code”, “Global_Dimension_1_Code”, “Global_Dimension_2_Code”, “Serial_No”, “Lot_No”, “Cost_Amount_Non_Invtbl”, “Cost_Amount_Expected_ACY”, “Cost_Amount_Actual_ACY”, “Cost_Amount_Non_Invtbl_ACY”, “Completely_Invoiced”, “Open”, “Drop_Shipment”, “Assemble_to_Order”, “Applied_Entry_to_Adjust”, “Order_Type”, “Order_No”, “Order_Line_No”, “Prod_Order_Comp_Line_No”, “Job_No”, “Job_Task_No”, “Dimension_Set_ID”, “Item_No_Link”, “Job_No_Link”, “Job_Task_No_Link”, “Cost_Amount_Expected”, “Reserved_Quantity”, “Qty_per_Unit_of_Measure”, “Sales_Amount_Expected”, “Entry_No”, “Location_Code”, “Shipped_Qty_Not_Returned”, “Sales_Amount_Actual”, “Cost_Amount_Actual”, “Invoiced_Quantity”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Removed Columns”,{{“Remaining_Quantity”, “Inventory_Quantity”}}),

#”Grouped Rows” = Table.Group(#”Renamed Columns1″, {“Posting_Date”, “Item_No”}, {{“InvQty”, each List.Sum([Inventory_Quantity]), type number}}),

#”Sorted Rows” = Table.Sort(#”Grouped Rows”,{{“Posting_Date”, Order.Ascending}})

in

#”Sorted Rows”

Creating the DAX for Our Visuals 

On the Inventory Performance report, I’ve added the ability to segment my quantity sold by growth rate over the previous period selected.  Here are the steps I used to add the changes to the report.

1. First, I clicked on the “Enter Data” icon from the Home tab in Power BI Desktop and entered the following information and named the table “Growth Segments”. 
2. The DAX Measure needed for our visulas:
   1. Total Qty Sold CY = SUM(‘Inventory Amounts'[InvQty])
   2. Total Qty Sold LY =

CALCULATE(

[Total Qty Sold CY],

SAMEPERIODLASTYEAR( ‘Date'[Date] ))

1. Qty Sold Growth = DIVIDE([Total Qty Sold CY],[Total Qty Sold LY],0)-1
2. Qty Sold per Growth Group =
3. CALCULATE (

[Total Qty Sold CY],

FILTER (

VALUES ( ‘Inventory Items'[Item No] ),

COUNTROWS (

FILTER (

‘Growth Segments’,

[Qty Sold Growth] >= ‘Growth Segments'[Min]

&& [Qty Sold Growth] < ‘Growth Segments'[Max]

)

)

> 0))

1. InventoryAvgAmt = SUM(‘Inventory Amounts'[Inventory Cost])/DISTINCTCOUNT(‘Inventory Amounts'[Posting_Date])
2. COGS_Actual = ABS(SUM(‘Inventory Transactions'[Cost_Amount_Actual]))
3. Turnover = [COGS_Actual]/[InventoryAvgAmt]
4. Inventory period = 365/[Turnover]

With all of the DAX Measures created we put them all together to create the Inventory Performance report (below) that now gives us the ability to track KPI to goals, see Sales and inventory quantity trends over time and visualize quantity sold per growth group compared to last period.

In my next post, I will create the Customer Sales Performance report to complete the enhance of the Business Central Sales Content pack.  Until then, go sign up for a free trial of Dynamics 365 Business Central.  You just need to provide your work email address and phone number.  Dynamics 365 Business Central is an all-in-one business management solution that helps organizations streamline business processes, improve customer interactions and enable growth by offering:

• Business without silos. Unify business and boost efficiency with automated tasks and workflows—all from within familiar Office tools like Outlook, Word, and Excel.
• Actionable insights. Achieve greater outcomes and gain a complete view across the business with connected data, business analytics, and guidance delivered by Microsoft’s leading intelligent technologies.
• Solutions built to evolve. Start quickly, grow and adapt in real time with a flexible platform that makes it easy to extend beyond Business Central based on evolving business needs.

Ready to Do Even More with Your Data?

Start organizing, knowing and executing on your data today with Business Central and Power BI to provide a self-service data lake in the future. KTL Solutions works with business leaders every day in helping them lead their organization into becoming a data-driven organization. If you need help with executing on your data, contact us today.