How to Sum Distinct Values in Power BI

Trying to sum values in a column in Power BI with duplicate entries can give you inflated, inaccurate numbers. If you only want to count a value once for each unique item - like summing shipping costs per unique order ID, not per product line item - a simple SUM won't work. This article will show you exactly how to solve this common problem using a few powerful DAX functions.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why a Simple SUM() Fails for Distinct Values

Imagine you have e-commerce data that looks something like this in a table called 'Sales':

OrderID | Product | SalePrice | ShippingCost

1001 | T-Shirt | 20.00 | 5.00 1001 | Hoodie | 45.00 | 5.00 1002 | T-Shirt | 20.00 | 5.00 1003 | Beanie | 15.00 | 3.00 1003 | T-Shirt | 20.00 | 3.00 1003 | Hoodie | 45.00 | 3.00

You want to calculate the total shipping revenue. If you create a simple measure like Total Shipping = SUM(Sales[ShippingCost]), Power BI will add up every single row in the ShippingCost column:

5.00 + 5.00 + 5.00 + 3.00 + 3.00 + 3.00 = $24.00

This is wrong. You've double and triple-counted the shipping cost for orders 1001 and 1003. The actual total shipping cost is based on the distinct orders: one $5.00 cost for order 1001, one $5.00 cost for order 1002, and one $3.00 cost for order 1003.

The correct total should be 5.00 + 5.00 + 3.00 = $13.00. To get this correct result, you need to tell Power BI to first find the unique order IDs and then sum the shipping cost associated with each one.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Best Way to Sum Distinct Values: SUMX with VALUES

The most common and effective way to handle this is by combining two DAX functions: SUMX and VALUES.

• VALUES(ColumnName): This function returns a one-column table containing the unique (distinct) values from the specified column. In our example, VALUES(Sales[OrderID]) would produce a temporary, virtual table that looks like this: {1001, 1002, 1003}.
• SUMX(Table, Expression): This is an "iterator" function. It goes row by row through the Table you give it, performs the Expression for each row, and then adds up all the results.

When you put them together, you tell SUMX to iterate over a unique list of OrderIDs created by VALUES, and for each unique OrderID, calculate the shipping cost. Because relationship context is preserved, it correctly finds the single shipping cost value associated with each unique order.

How to Create the Measure in Power BI

Let’s build the DAX measure to calculate the correct shipping cost.

1. In Power BI Desktop, find your table in the Data pane on the right. Right-click on the table name (e.g., 'Sales').
2. Select New Measure from the context menu.
3. The formula bar will appear at the top. Enter the following DAX formula:

Correct Total Shipping =
SUMX (
    VALUES ( Sales[OrderID] ),
    CALCULATE ( MIN ( Sales[ShippingCost] ) )
)

Breaking Down the Formula:

• SUMX (...): Starts the iterator function.
• VALUES ( Sales[OrderID] ): Generates our virtual table of unique order IDs: {1001, 1002, 1003}. SUMX will loop through this list.
• CALCULATE ( MIN ( Sales[ShippingCost] ) ): For each unique OrderID, this expression is evaluated.

When you add this "Correct Total Shipping" measure to a card visual, it will now display the accurate result of $13.00.

Graphed

Still Building Reports Manually?

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

Watch the Demo

An Alternative Approach: Using DISTINCT

You might also see solutions using the DISTINCT function instead of VALUES. The syntax is nearly identical.

Correct Total Shipping (Distinct) =
SUMX (
    DISTINCT ( Sales[OrderID] ),
    CALCULATE ( MIN ( Sales[ShippingCost] ) )
)

For most simple cases like this one, VALUES and DISTINCT behave identically. They both return a unique list of values from the specified column. The main technical difference is that VALUES can return a blank row if there are data integrity issues in your model's relationships, while DISTINCT will not. For summing distinct values, either one will generally work, but VALUES is often preferred as standard practice.

Handling More Complex Scenarios with FILTER

What if you want to calculate the sum of distinct shipping costs, but only for orders that contain a specific product, like a 'Hoodie'? We can expand our formula by adding a FILTER function.

The FILTER function takes a table and applies a filter condition to it, returning only the rows that meet the condition.

Hoodie Order Shipping Costs =
SUMX (
    FILTER (
        VALUES ( Sales[OrderID] ),
        CALCULATE ( COUNTROWS ( Sales ), Sales[Product] = "Hoodie" ) > 0
    ),
    CALCULATE ( MIN ( Sales[ShippingCost] ) )
)

Graphed

Still Building Reports Manually?

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

Watch the Demo

Let’s break this more advanced formula down:

• Our iterator SUMX isn't looping over all unique OrderIDs anymore. Instead, it’s looping over a table we create with FILTER(...).
• FILTER ( VALUES ( Sales[OrderID] ), ... ): We are filtering our list of unique Order IDs.
• CALCULATE ( COUNTROWS ( Sales ), Sales[Product] = "Hoodie" ) > 0: This is the filter condition. For each OrderID, it counts how many rows have that OrderID and have the 'Product' column equal to "Hoodie". If the count is greater than zero, it means the order contained a hoodie, and the OrderID is kept. In our example data, only OrderIDs 1001 and 1003 contained a 'Hoodie'.
• CALCULATE ( MIN ( Sales[ShippingCost] ) ): This expression is then run only for the filtered OrderIDs (1001 and 1003), summing their shipping costs ($5.00 + $3.00) to get a total of $8.00.

Common Mistakes and Best Practices

Working with iterators and filter context can be tricky. Here are a few things to keep in mind:

• Check Your Relationships: DAX functions rely heavily on the relationships defined in your data model. Make sure your tables are properly connected. If the relationship between your sales table and a product table is incorrect, your calculations may produce unexpected results.
• SUM vs. SUMX: Remember the fundamental difference. SUM aggregates an entire column as is. SUMX iterates through a table (either a real one or a virtual one you create) and performs a calculation row by row before summing the results. When dealing with distinct value calculations, you will almost always need SUMX.
• Format Your DAX Code: For complex formulas, use Shift + Enter to add line breaks and tabs to indent your code in the DAX formula bar. This makes it much easier to read, understand, and troubleshoot.

Final Thoughts

Mastering how to sum values based on a distinct count of another column is a huge step in moving from basic to intermediate Power BI skills. Using iterator functions like SUMX combined with thoughtful table functions like VALUES, DISTINCT, and FILTER gives you the flexibility to solve almost any reporting scenario where simple aggregations fall short.

While learning DAX is a powerful skill, we know it can have a steep learning curve and sometimes feel like you're writing code instead of analyzing data. This complexity is why we created Graphed. Instead of writing formulas, you can simply connect your data sources - like Shopify, Google Analytics, or Salesforce - and ask questions in plain English, like "What was our total shipping cost last month?" Our AI Analyst instantly generates real-time dashboards and reports for you, so you can spend less time wrangling formulas and more time acting on insights.