Power BI Calculated Columns: Unlocking Data Insights

Power BI is a tool that helps business owners to effectively visualize and analyze their data. One of the major features of Power BI is its capacity to add calculated fields to the data.

Calculated fields give users the room to perform complex calculations on your data, and create new variables that can be used during analyses. In this guide, you’ll discover why adding calculated columns in Power BI is important, learn tips and tricks for adding a calculated field in Power BI, and learn the advantages of adding DAX calculated columns.

A calculated field is a custom field that users create in Power BI by defining a new formula. The formula can use mathematical operators, existing fields in your data, and functions. Calculated fields come in handy when certain calculations are not available in the source data, or when you have to combine data from multiple sources.

First…

What are Power BI Calculated Columns?

Calculated fields are used in creating new dimensions (or measures) that are not present in the original data. For instance, you can create a calculated field that calculates the profit margin by subtracting the cost of goods sold from the revenue and dividing the result by the revenue.

It’s important to emphasize that calculated fields are not stored in the source data. They’re calculated on the fly when the report is refreshed or the data is updated. Therefore, if the underlying data changes, the calculated field will also change accordingly.

Why is Adding Calculated Columns in Power BI Important?

Calculated fields play a major role in Power BI since they give users room to perform complex calculations that cannot be done using the source data alone. For instance, a user can create a calculated field to calculate the Profit Margin of a business based on the sales revenue and expenses.

Calculated fields enable you to gain insights from your data, and that can lead to data-driven decision-making.

Calculated fields are also used in the creation of custom metrics that are specific to the business needs. For instance, users can create a calculated field to measure the customer lifetime value, and that is a crucial metric for businesses that rely on repeat customers.

What are the Tips and Tricks for Adding a Calculated Field in Power BI?

Here are some useful tips and tricks to help you add a calculated field in Power BI:

• Break down the calculation into smaller, manageable parts
• Use parentheses to control the order of operations
• Use variables to store values that are used repeatedly.

What are the Advantages of Adding a DAX Calculated Column?

• Enhanced Data Analysis: DAX calculated columns offer additional flexibility for data analysis, and it allows the user to derive new insights and metrics from existing data.
• Seamless Integration: DAX calculated columns seamlessly integrate with Power BI and other tools. This enables a smoother and more efficient workflow for data analysis and modeling.
• Simplified Formulas: DAX simplifies the process of creating complex formulas. That makes it easy for users to generate calculated columns without extensive programming knowledge.
• Consistency in Reporting: Calculated columns contribute to standardized and consistent reporting. That is achieved by ascertaining that specific calculations are uniformly applied across datasets.
• Improved Visualization: Calculated columns give room for the creation of custom data visualizations and reports. It offers a more comprehensive view of the data and aids in better decision-making.

How to Use Custom Visuals with Your Calculated Fields in Power BI?

Here are easy steps to follow when creating a calculated column in Power BI.

• Navigate to the Data view in Power BI Desktop.

• Select the Sales table from the Fields pane.

• Click on the New Column button in the Modeling tab.

• Enter a name for the column (like Profit).
• Enter the formula for the column in the formula bar.
  • Profit=Sales[SalesAmount] – (Sales[OrderQuantity] * RELATED(’Product’[StandardCost]))
• Click Enter to create the column.

What are the Advanced Techniques for Analyzing Data With Calculated Fields in Power BI?

Stage 1: Log into Power BI, enter your email, and click “Submit.”

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Create a Data Set and Select the Data Set to Use in the Sankey Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the data table above into the “Power Query” window. After that, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If a data set has not been created, you’ll get an error message.

• Choose the data set to be used in the creation of the Sankey diagram. Power BI populates the screen.

• Click on the “Create a report” dropdown. Select “Start from scratch.”

• A Report Canvas screen appears on a screen.

Stage 3: Add the Power BI Sankey Diagram Extension by ChartExpo

• To create a Sankey Diagram, you’ll have to use an add-in or Power BI visual from AppSource. Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo.

• In the following menu that opens, select the “Get more visuals” option.

• In the window that opens, enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box. After that, you’ll see the “Sankey Diagram for Power BI by ChartExpo.”

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Draw the Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. A window similar to the one below will display in the report section of your dashboard.

• There’s the option to resize the visual. Navigate to the right side of the Power BI dashboard. You’ll see “Fields” next to “Visualizations.”

• Follow the sequence below when selecting fields.
• • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll have to provide your email address or a ChartExpo license key.

Stage 5: Activate the ChartExpo Trial or Apply the Subscription Key

• Select the ChartExpo visual. You’ll see three icons below “Build Visual” in the Visualization panel.

• Select the middle icon, “Format visual.” The visual properties will be populated.

• As a new user, you’ll have to enter your email address in the textbox under the “Trial Mode” section. Toggle “Enable Trial” to activate the 7-day trial.

• The Sankey Diagram created under the 7-day trial comes with the ChartExpo watermark.

• If you have a license key, enter it in the “ChartExpo License Key” textbox in the “License Settings” section. Slide the toggle switch next to “Enable License” to “On.”

• The Sankey diagram does not come with a watermark.

• You can add a Prefix (like the $ sign) with the numeric values in the chart. Expand the “Stats” properties and include the Prefix value.

• You can add colors to each node. Expand the “Level Colors” properties and select the colors.

• The changes will be made automatically.

Insights

• At Level 1 (Total Cost), the procurement cost is $155K.
• At Level 2 (Company Type), out of the $155K cost, $72.3K (46.7%) was allocated to the supplier, while $82.4K (53.3%) was spent on subcontractors.
• At Level 3 (Company Name), the supplier cost of $72.3K was divided between two companies: Five-star Construction and Power-up Builder, with charges of $19.3K and $53.0K, respectively.

Using Power BI Calculated Columns to Improve Chart Accuracy:

1. Open your Power BI Desktop or Web.
2. From the Power BI Visualizations pane, expand three dots at the bottom and select “Get more visuals”.
3. Search for “Sankey Chart by ChartExpo” on the AppSource.
4. Add the custom visual.
5. Select your data and configure the chart settings to create the chart.
6. Customize your chart properties to add header, axis, legends, and other required information.
7. Share the chart with your audience.

The following video will help you create a Sankey Chart in Microsoft Power BI.

FAQs

How do I add two calculated columns in Power BI?

To add two calculated columns in Power BI, you have to navigate to the “Data” view, select a table, click “New Column” twice, and write the DAX formula for each column. After that, press Enter for each.

How do you calculate two columns in DAX?

Use the “New Column” option in Power BI to create a new calculated column for each desired calculation. After that, write the corresponding formulas for each column, and press Enter.

Wrap Up

Power BI calculated columns are user-defined columns computed using DAX expressions. They offer dynamic calculations based on existing data and also facilitate advanced analysis and insights within reports.

It plays a major role in performing complex calculations, creating custom metrics, and enhancing data visualization capabilities.

But why should you add a DAX calculated column? First, it simplifies the process of creating complex formulas and makes it easy for users to generate calculated columns without extensive programming knowledge.

It gives room for the creation of custom visualizations and reports, and that provides a more comprehensive view of the data, and aids in better decision-making.

Adding DAX calculated columns offers a high degree of flexibility, and allows users to adapt their analysis to meet the ever-changing business requirements.

Now you know the benefits of Power BI calculated columns, how will you incorporate them into your analytic process?