How to use an aggregation spanning two 1-directional 1-to-n relationships?

[Albert_0122]

Hi everyone,

I am currently building a report for my company and encountered the following problem: Table1 holds Orders containing Products (Table2) and Product Sets (Table2). Product Sets can contain Sub-Products (Table3). For a given value in the filter-columns (both in Table2 and Table3) we want to sum the total cost for that filter-value in the given Order. Something important to note is, that the quantity of sub-products needs to be multiplied by the quantity of their respective Product-Sets.

So we basically want to do the following measurement in Table1:

for a given filter-value f:  quantity_2*cost_2 + cost_3*quantity_3*quantity_2

Is there a way to do this directly using a DAX formula? Would this be a use-case, where one needs to build a temporary table using the SUMX function?

[v-jiewu-msft · Accepted solution]

Hi @Albert_0122 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new measure to calculate value.

filter value = 
SUMX(
    Table2,
    Table2[quantity_2] * Table2[cost_2] +
    SUMX(
        RELATEDTABLE(Table3),
        Table3[cost_3] * Table3[quantity_3] * Table2[quantity_2]
    )
)

3.Drag the measures into the matrix visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Albert_0122]

Thank you very much! Your solution works

[lbendlin]

for a given filter-value f:  quantity_2*(cost_2 + cost_3*quantity_3)

Sounds reasonable. What have you tried and where are you stuck?

[Albert_0122]

Hi lbendlin,

thanks for your reply. I initially solved this issue in Power Query during Ingestion by merging queries and introducing quantity_2 to Table3 (where it does not really belong). Merging the queries has an effect on performance during Ingestion. We will reimplement this report for Direct Query and I wanted to test, if solving the issue in DAX will ultimately be the superior version, since only one record from Table1 will be needed at any time.

I have never used SUMX or similar methods before and was wondering if this is the correct approach for this issue. I will try the solution provided by v-jiewu-msft.

[v-jiewu-msft]

Hi @Albert_0122 ,

If it helps after trying it, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu