Microsoft Power BI continues to provide our clients with up to date information to continue making great business decisions. An important aspect of Power BI is to optimize it to make sure the users are seeing the most relevant information. One way to make sure the visualizations and tables are reflecting the data concisely is to use DAX to create the necessary columns.

Microsoft Power BI displays Income and Cost of Goods Sold amounts, based on Account Categories and Dimension Value Codes found within Dynamics 365 Business Central. The visualizations below of a Power BI Table are referred to as Before and After in this blog.

As outlined in previously published blogs, the Business Central tables containing this data are Chart of Accounts, Dimension Set Entries, and General Ledger Entries (GL Entries).

Before:

After:

Although the data displayed within both objects is accurate, the After table offers the better presentation.

Within After, we have Income and Cost of Goods Sold amounts residing in separate columns and we have a column for Profit Margin.  We’re able to analyze amounts for Dimension Value Codes based on Account Category, without having to display the Account Categories!

We are able to present the data in a more concise and accurate table by utilizing DAX.

What is DAX?

DAX stands for “Data Analysis Expressions”.  According to Microsoft Learn, DAX is a “collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.” Microsoft Learn, further, states that DAX creates new information from data already within your Power BI model. Using DAX allows you to create new columns within Power BI.

DAX Formulas

Again, the formulas or measures created using DAX are Income, Cost of Goods Sold, and Profit Margin:

• Income = (sumx(FILTER(‘G_LEntries’, Related(‘Chart_of_Accounts'[Account_Category])=“Income”), ‘G_LEntries'[Amount]))*-1
• Cost of Goods Sold = sumx(FILTER(‘G_LEntries’, Related(‘Chart_of_Accounts'[Account_Category])=“Cost of Goods Sold”), ‘G_LEntries'[Amount])
• Profit Margin = [Income]-[Cost of Goods Sold]

Creating a DAX formula begins, by clicking the “New Measure” button:

After typing the name of the measure, such as “Income” or “Cost of Goods Sold”, you enter DAX functions as follows:

• Sumx: returns the sum of an expression evaluated for each row in a table
• FILTER: extracts data from a table based on specified criteria such as “Account Category”
• RELATED: returns a related value from another table (“Income” or “Cost of Goods Sold”)

If you have any questions about Power BI data visualizaations, please do not hesitate to reach out to us at Support@BondConsultingServices.com or you can click here to schedule a consultation with one of our experts.