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

PowerBI: Compare different Timespans and Products in one Matrix

A recent requirement at a customer was to compare Sales Quantity and Sales Totals of different time spans in one matrix. The customer is selling saisonal as well as all-time products. It should be possible to compare the sale of saisonal products from last season to the actual sales.

Data Model

The original data contains 6 rows: The product, the corresponding collection, the sold quantity, the unit price, calculated total price (Qty * Price), date.

In order to make the Quantity and Totals available for a row-like visualization the original data was unpivoted. This created a new column called “Attribute” and another Column called “Value”. The unit (pcs, €) have been added to the attribute to make it more readable. The resulting data looks like the following in PowerBI:

Filter tables

To implement 2 independet filter options based on date and collection 4 independet tables have been added. A date table and a collection table for both filter options. The date tables can easily be generated by using the Calendar() function:

Filter1Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))

Filter2Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))

The collection tables are generated by using the Distinct() function on the collection column in the sales data table. Each of the tables contain a distinct list of collections available for filtering.

 Filter1Collection = DISTINCT(Sales[Collection])

 Filter2Collection = DISTINCT(Sales[Collection])

Measures

Three measures have been create. Two measures that sum up the Value column in the sales data filtered on the selected values in the Filter1* table or Filter2 table:

M_Sum1 = CALCULATE(
    SUM(Sales[Wert]),
    FILTER(
        Sales,        
        Sales[Date] >= FIRSTDATE(Filter1Date[Date]) &&
        Sales[Date] <= LASTDATE(Filter1Date[Date]) &&
        Sales[Collection] in VALUES(Filter1Collection[Collection])
        )
    )

M_Sum2 = CALCULATE(
    SUM(Sales[Wert]),
    FILTER(
        Sales,        
        Sales[Date] >= FIRSTDATE(Filter2Date[Date]) &&
        Sales[Date] <= LASTDATE(Filter2Date[Date]) &&
        Sales[Collection] in VALUES(Filter2Collection[Collection])
        )
    )

The third measure calculates the difference between the other two measures:

M_Diff = [M_Sum2]-[M_Sum1]

Visuals

Finally the report contains 5 visuals. Two slicers for date and collection on the left, another two slicers for date and collection on the right, and a matrix in the middle. The matrix shows the Attribute column as row and shows M_Sum1, M_Sum2 and M_Diff as values.

For example comparing collection “Summer 2021” from 1. Jan. 2021 till 30. Sep. 2021 to the aggregated values of “Collection 2022” + “Basic Wear” from 1. Jan 2022 till 30. Sep. 2022.

The slices on the left only affect the M_Sum1 measure which shows quantity and totals in the Filter1 column while the slicers on the right effect only M_Sum2 measure which shows the results in Filter2 column. The difference is calculate dynamically when adjusting the slicers.