Wow! Microsoft’s Business Application Summit just got over and I can’t believe all of the great things that were announced.

Here is a list of some of the features that are coming:

• Integration with Python – Connector and custom visuals like R
• Composite model – Composite models allow you to mix import and tabular direct query sources as well as have multiple tabular DirectQuery sources.
  • This lets you augment your enterprise data models with imported tables. Modelers can create a Power BI Desktop file over a DirectQuery datasource, and then also add tables that are imported from another data source.
  • To support this new model structure, we are also introducing many-to-many relationships and tables that are in dual mode, which means they can act as import or DirectQuery, depending on what other tables you add to the visuals. Power BI will then intelligently serve answers to your analyses from either the imported data or by pushing a query to the underlying datasource.
• Aggregations – Massive volumes of data require new ways of storing information to balance the needs of slice-and-dice interactive analysis with deep, detail-level reporting.
  • Aggregations allow model developers to surface cached values at a high level for interactive analysis, but still lets users drill down to detailed data that is queried from the underlying data.
  • You can create DirectQuery models over massive-scale data sources such as Spark clusters, or massive data warehouses.
  • For interactive analysis, running queries directly against these datasets is impractical. But for datasets that could be as large as hundreds of terabytes, the data cannot all be cached in memory.
  • Aggregations lets you cache just aggregate data into memory for fast access. You define tables in your data model as an aggregate table, linked to tables at the detail level. The detail tables stay in DirectQuery mode but the aggregates are defined as being in dual mode. So the data is also cached in memory at the aggregate level.
  • If users run queries or create visuals that can be answered from the in-memory cache, the results are retrieved from there. But if the query requires the detail data, it’s pushed down to the underlying DirectQuery source dynamically. The end user doesn’t see any difference in experience in their Power BI report.
• Intellisense support for the M formula language
• Smart Data Prep – Data profiling and fuzzy-logic matching

Find out more about these features and more by viewing the On Demand sessions from the Business Applications summit– October 2018 Release Overview

Now on to part two of my Create Awesome Business Central Data Analysis.

In my last post, How to Create Awesome Business Central Data Analysis, one of the things I discussed was how to connect Power BI to Dynamics 365 Business Central and build an Inventory Performance report. Today we are going to create a sales performance visual.

Creating our Sales Performance Visual

Here are the data tables we are going to import to analyze our Business Central sales data:

• SalesDashboard
• Customers
• Customer List

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

1. Rename several columns
2. Remove several columns listed below in the M Code.
3. Add a custom column to calculate Profit

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

Now with all three Business Central view imported, we can Save & Apply our changes.

Creating the DAX for our Sales Performance Report

On the Sales Performance report, I’ve added the ability to segment my Accounts Receivable into aging buckets. 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 “AR Buckets”.

2.  The DAX Columns & Measures needed for our visuals:

With the Columns and Measure created for the Aged Account Receivable visual, let’s work on our Gross Sales, Revenue Total, Revenue Percent, Cost Total and Cost Percent DAX Measures.

With all of the DAX Columns and Measures created we can now put them all together to create the below Sales Performance report that now gives us the ability to track KPI to goals, see Sales, Profit and Cost trends over time. Here is how we create our visuals:

• Gross Sales KPI – add the Gross Sales CY measure to the Indicator field in the Visualization pane, Calendar Year from Date dimension to Trend axis and Gross Sales LY measure to Target goals.
• Gross Sales and Profit Gauge – Add Gross Sales to the Value field of the Visualization pane and Profit to the Minimum value field.
• Aged Accounts Receivable clustered bar chart – Add ARBucket column from Customer List to the Axis field in the Visualization pane and Aged_AR measure to the Value field.
• Cost vs Revenue % by Product EnhancedShatter chart – Add Item Description to the Details field in the visualization pane, RevenuePct measure to the X Axis field, SalesCostPct to the Y Axis field, Sales_Amount_Actual field to the Size field and Profit to the Color saturation field.
• Current to Prior Year Sales by Month clustered column chart – Add Calendar Month from the Date dimension to the Axis field in the visualization pane, Gross Sales CY and Gross Sales LY measures to the Value field.
• Gross Sales by State line and clustered column chart – Add State from the Customers dimension to the Shared Axis field in the visualization pane and Gross Sales to the Columns Value field.

Start your Power BI learning for free and bring your company data to life!

Power BI transforms your company’s data into rich visuals for you to collect and organize so you can focus on what matters to you. Stay in the know, spot trends as they happen, and push your business further.

Looking for new ways to find and visualize data and to share your discoveries? Power BI can help. This collection of tools, online services and features from Microsoft transforms your company’s data into rich visuals for you to collect and organize, so you can spot trends, collaborate in new ways, and make sense of your data.

Start your Power BI learning at some of these great sites:

• Microsoft Power BI Guided Learning
• Chris Webb’s BI Blog
• Guy in a Cube
• Radacad Power BI
• Sqlbi.com
• Data Veld Power BI
• Enterprise DNA

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. Need help executing on your data? Contact us today.