6 ways to aggregated a value from a related table in PowerBI

Aggregating values from a related table is a common task in PowerBI. However, there are many ways how to achieve this goal. Here are a few examples:

Note: I'm using an Adventure Works Lite Demo DB and using SalesOrderHeader + SalesOrderDetail to aggregate the LineAmount from the details.

1) Aggregate at data source

If you have the option to push aggregation logic to the data source you should do so. Especially if the data source is SQL Server because its designed to execute queries and perform aggregations in a very effective way.

select h.SalesOrderID, sum(d.LineTotal) as SumLineTotals 
from SalesLT.SalesOrderHeader as h
left outer join SalesLT.SalesOrderDetail as d
on h.SalesOrderID = d.SalesOrderID
group by h.SalesOrderID

2) Join in Power Query

Load both tables using PowerQuery. Select the SalesOrderHeader table. From Home tab, select Merge Queries. Join both tables via SalesOrderID and select LEFT OUTER JOIN as join type.

Navigate the joined table but select Aggregate instead of Expand. Aggregate the LineTotals from the SalesOrderDetails.

3) Calculated Column in Power Query (M)

You can also use PowerQuery to sum up values from another table. Note “Rename” is the name of the prev. Step in my PowerQuery.

= Table.AddColumn(Rename, "LineTotalsPQ", 
each let no = [SalesOrderID] 
in List.Sum(
Table.SelectRows(#"SalesLT SalesOrderDetail", each [SalesOrderID] = no)[LineTotal]
))

4) Calculated Column in Data Model (DAX)

Create a new column at the SalesOrderHeader in the data model using SUMX function.

Col_SumLineTotal = SUMX(
FILTER('SalesLT SalesOrderDetail',
'SalesLT SalesOrderDetail'[SalesOrderID] = 
EARLIER('SalesLT SalesOrderHeader'[SalesOrderID])),
'SalesLT SalesOrderDetail'[LineTotal])

5) Measure

Make sure you have an active 1:n relation from SalesOrderHeader to SalesOrderDetails via SalesOrderID. Create a new measure on the SalesOrderHeader using DAX.

M_SumLineAmount = SUM('SalesLT SalesOrderDetail'[LineTotal])

Add a table visual to the report and place the SalesOrderID and the measure from the SalesOrderHeader table.

6) Visual

Make sure you have an active 1:n relation from SalesOrderHeader to SalesOrderDetails via SalesOrderID. Add a table visual and place the SalesOrderID from the SalesOrderHeader table. Add the LineAmount from the SalesOrderLines and choose to aggregate as Sum.