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.

PowerBI: Filter an unrelated table using Measures

In some cases you want to filter a table based on a selection which is not related to the table. For example the SalesTable is realted to the CustTable via the Account. The QuotationTable is related to the SalesTable (QuotationID -> Origin). The QuotationTable also has an Account field but is not related to the CustTable.

Such a situation can be avoided by using a better design approach, however you may come across such a problem one day 😉

Compare SelectedValue

Create a new measure in the CustTable that returns the selected value. Place a table visual showing the CustTable in the report. For debugging reasons you may want to place a card visual with your measure as well.

M_SelectedValue = SELECTEDVALUE(CustTable[Account])

In the not-related table (QuotationTable) create a new measure that returns the account number which will be used to filter the records

M_Account = SELECTEDVALUE(QuotationTable[CustAccount])

Add a second measure to compare the selected CustTable value to the CustAccount value in the Quotation table and returns a numeric value (not a boolean). If there is no CustTable selected return e.g. 2, if the selected values are equal return 1, else return 0.

M_Filter = IF(
    ISBLANK([M_SelectedValue]),2,
    If([M_SelectedValue] = [M_Account], 1, 0)
    )

Place a table visual in your report showing the QuotationTable and both measures

Configure a filter on the visual to show only records where the M_Filter value greater than 0.

If no customer is selected all quotations are shown because the M_Filter is 2.

If a customer is selected M_Filter is 1 for all matching records or 0 otherwise.

Now you can hide the M_Filter in the QuotationTable so your users don’t get confused