How to Multiply Columns in Power BI

Performing calculations is a core part of any data analysis, and multiplying columns is a fundamental task, whether you're calculating sales revenue, total costs, or weighted scores. For Power BI users, there are a few ways to get this done. This tutorial will walk you through the primary methods for multiplying columns in Power BI, explaining not just the "how" but also the "why," so you can pick the best approach for your specific report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Creating a Custom Column in Power Query

The Power Query Editor is Power BI's built-in tool for extracting, transforming, and loading data (ETL). Performing multiplications here means you are creating a new, permanent column in your data table before it's even loaded into your Power BI data model. This is the best practice for data preparation and transformation.

Why Use This Method?

Think of Power Query as your data workshop. You want to shape your data, clean it up, and get it ready here. Adding a column that represents a calculation like Quantity * Price is a perfect job for Power Query. The calculation happens once when the data is refreshed, making it efficient. The resulting column is then available in your data model just like any other column from your original source.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions

1. Open the Power Query Editor: In your Power BI Desktop file, click on the Transform data button in the Home ribbon. This will open the Power Query Editor in a new window.
2. Select Your Table: On the left side of the Power Query Editor, in the "Queries" pane, select the table where your columns live. For this example, let's assume we have a table named Sales with [Quantity] and [UnitPrice] columns.
3. Navigate to the "Add Column" Tab: At the top of the window, click on the "Add Column" tab in the ribbon. Here you will find various options for creating new columns.
4. Choose "Custom Column": Click on the Custom Column icon. This will open a new dialog box where you can define your calculation.
5. Write the Multiplication Formula: In the "Custom Column" window, you'll see a few fields to fill out:
6. Check for Syntax Errors: Power Query will give you a little checkmark at the bottom if your formula has no syntax errors. If it detects an issue, double-check your column names and the multiplication operator (*). Click OK.
7. Set the Data Type: Your new Revenue column is now at the far right of your table. Power Query often guesses the data type, but it's crucial to check it. Click the icon on the column header (it might show "ABC 123") and set it to the appropriate type, like Fixed decimal number or Currency. This ensures calculations in your reports are accurate.
8. Close & Apply: Finally, click the Close & Apply button in the top-left corner of the "Home" tab. This saves your changes and loads the updated table, including your new calculated Revenue column, into the Power BI data model.

Method 2: Creating a Calculated Column with DAX

Another way to multiply columns is by using DAX (Data Analysis Expressions), which is the formula language for Power BI. In this method, you add a column directly within the data model after the data has been loaded. This is done in the main Power BI Desktop view, not in the Power Query Editor.

Why Use This Method?

A DAX calculated column should be used when your calculation depends on other DAX calculations or needs to use functions that aren't available in Power Query. Like the Power Query method, it creates a new "physical" column in your table which is stored in the model. This makes the created column good for using as a slicer, filter, or on an axis of a visual.

Be aware: a DAX calculated column consumes memory and increases the file size because the result for every single row is stored. For simple multiplications, Power Query is often the better choice.

Step-by-Step Instructions

1. Go to the Data View: In the main Power BI Desktop interface, click on the spreadsheet-like icon for Data view on the left-hand pane.
2. Select Your Table: From the Fields pane on the right, make sure you have the correct table selected (e.g., Sales). This ensures the new column is added to the right place.
3. Create the New Column: From the Table tools ribbon that appears at the top, click on New column. A formula bar will appear above your data table, much like in Excel.
4. Write the DAX Formula: In the formula bar, you'll need to define your new column using DAX syntax. The format is ColumnName = [Formula]. DAX formulas require you to reference both the table name and the column name.
5. Format Your New Column: Your new DAX column will appear in your table. To format it, select the column header, and the Column tools ribbon will appear. Here, you can change the format to Currency and select your desired currency symbol.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: Calculating Dynamic Totals with a DAX Measure

This is probably the most common and powerful way to perform calculations in Power BI, but it's important to understand how it's different. Unlike the first two methods, a measure does not create a new physical column in your table. Instead, it’s a virtual calculation that is performed on the fly based on the context of your visualization (like filters, slicers, or a matrix).

Why Use This Method?

Measures are the foundation of interactive reporting. If you want to see your total revenue on a scorecard, or slice it by product category, or see it trended over time, a measure is what you need. They are extremely efficient with file size because they aren't stored row-by-row. Measures calculate aggregations over many rows.

To multiply columns and then sum the result up for a total, we use an "iterator" function like SUMX.

Step-by-Step Instructions

1. Navigate to the Report View: Click the bar chart icon for Report view on the left-hand pane. This is where you build your visualizations.
2. Create a New Measure: Right-click on your table in the Fields pane (e.g., Sales) and select New measure. Alternatively, you can click on the Sales table and then click New measure in the "Home" ribbon.
3. Write the SUMX Formula: A formula bar will appear. Here, you'll use the SUMX function. SUMX tells Power BI to go through a specified table, row by row, perform a calculation, and then sum up the results of that calculation. It's essentially a SUM of a MULTIPLY in one function.
4. Press Enter: When you press Enter, the measure doesn't visually change the table. Instead, a new item with a calculator icon appears in your Fields list called Total Revenue.
5. Use the Measure in a Visual: Now for the cool part. You can add a Card visual onto your report canvas and drag your new Total Revenue measure into the "Fields" area. It will instantly show the grand total. If you create a table visual and add a field like Product Category, then add the Total Revenue measure, it will dynamically calculate the revenue for each category - all from that one measure.

When Should You Use Each Method?

Choosing the right method is essential for building efficient and scalable Power BI reports.

Use a Custom Column (Power Query):

• When preparing or transforming your data ahead of time.
• When you need a permanent, static column at the lowest grain of your data (e.g., a "LineTotal" for every single transaction).
• It's the most efficient choice for simple, row-level arithmetic as the work is done during data refresh.

Use a Calculated Column (DAX):

• When your calculation contains complex DAX logic or depends on results from other calculated columns or measures.
• If you absolutely need a physical column to use as a slicer, a filter, or category label.
• Use sparingly, especially on very large tables, as it consumes RAM and adds to your file size.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Use a Measure (DAX):

• This should be your default for nearly any calculation shown in a report visual.
• Use when you are calculating aggregations (sums, averages, counts) over potentially many rows of data.
• Measures are dynamic and respond to whatever filters a user applies in the report, making them perfect for interactive dashboards.

Dealing with Common Errors

Sometimes your multiplication won't work perfectly on the first try. Here are two frequent issues:

• Data Type Mismatch: You can't multiply a number by a piece of text. If you try, Power BI will give you an error. The solution is always to go back to the Power Query Editor, find the text-based column, and convert its data type to a whole or decimal number before you try any calculations.
• Handling Blanks or Zeros: Multiplying a number by zero is simply zero. However, calculating with BLANK() values can sometimes produce unexpected results or errors depending on the DAX function. If some rows might have no value for Quantity or UnitPrice, you can add logic to handle it, like treating blanks as zeros. For a DAX calculated column, you could write: =IF(OR(ISBLANK('Sales'[Quantity]), ISBLANK('Sales'[UnitPrice])), 0, 'Sales'[Quantity] * 'Sales'[UnitPrice]).

Final Thoughts

Multiplying columns in Power BI is straightforward once you know your options. You can add a static column during data preparation with Power Query, a row-by-row calculated column with DAX, or a powerful, dynamic aggregate calculation with a DAX measure. Picking the right tool for the job - Power Query for transforming and measures for reporting - will make your dashboards faster, more efficient, and easier to manage.

Steps like a simple column multiplication are just the first of many manual tasks in data reporting. Rather than writing formulas and DAX measures yourself, a better way is emerging. With Graphed, we’ve made analysis as simple as a conversation. Instead of multi-step processes in BI tools, you can simply ask, "Show me our total revenue calculated by multiplying quantity and unit price, broken down by country," and get an interactive chart in seconds that’s connected directly and in real-time to all your data sources.