DAX to calculate sum of related tables for total sales?

[pe2850]

I'm having a hard time correcting my DAX syntax to calculate total sales $. 

My model structure has a contracts table, with child tables that contain the actual price. The relationship is 1:Many from contracts to products, support, services tables, with cross filter in both directions. 

My table structure looks like this:

contract.ID [PK] | contract.sales_tax

contract_support.cid [FK] | contract_support.price

contract_sales.cid [FK] | contract_sales.price

contract_service.cid [FK] | contract_service.price

My DAX looks like this:

_TotalDeal =  SUMX(contract_support, RELATED(contract_support[price])) + SUMX(contract_sales, RELATED(contract_sales[price])) + SUMX(contract_service, RELATED(contract_service[price])) + contract.sales_tax

• Should I use a measure here? Every row in table contract should have a deal amount, but the deal can contain many rows from the child tables?
• Why is DAX saying that either the column does not exist, or there is no relationship? The relationships do exist across all those tables and are active. 
• Is my use of SUMX and RELATED correct?

Thanks! I'm stumped

[TeigeGao · Accepted solution]

Hi pe2850,

As mentioned by @hnguy71, we can use the sum for each column and then add them together.

_TotalDeal = SUM('contract_sales cid'[contract_sales.price])+SUM('contract_service cid'[contract_service.price])+SUM('contract_support cid'[contract_support.price])+ SUM('contract ID'[contract.sales_tax])

>>Should I use a measure here? Every row in table contract should have a deal amount, but the deal can contain many rows from the child tables?

We can use measure here, when we drag the contract_id and the measure to a visual, it will create a context for each row in table contract, the measure will calculate in these context and return different result for each row.

>>Why is DAX saying that either the column does not exist, or there is no relationship? The relationships do exist across all those tables and are active.

In your scenario, you have a table ‘contract.ID’ which contains the column [contract.sales_tax], we need to use the 'contract ID'[contract.sales_tax] to represent this column, besides, in a measure, we need to give a scalar to it, as a result, we need to use the SUM('contract ID'[contract.sales_tax]) to return a scalar.

>>Is my use of SUMX and RELATED correct?

There is no need to use SUMX or RELATED function, SUMX() returns the sum of an expression evaluated for each row in a table, for example, I need to calculate the sum of some columns for each row ( = SUMX (table_name, column1+column2)), for RELATED, you have created the relationship between these tables, you needn’t to use this function.

Best Regards,

Teige

[hnguy71]

If they have a relationship you dont need to use related. Also, you're not filtering by anything so you don't need sumx.

I would suggest doing 3 different measures (just in case you want to re-use these sum elsewhere ie, percentage total, mtd, etc):

Measure1 = SUM(contract_support[price])

Measure 2 = SUM(contract_sales[price])

Measure3 = SUM(contract_service[price])

Combine _TotalDeal

_TotalDeal = [Measure1] + [Measure2] + [Measure3] + [My_Contract_Sales_Tax]