DAX to calculate sum based on another table

[manojsv]

Hello All,

I am trying to create a measure to calcualte the sum of cost of all products from another table. Cost information are stored in Table 1(Dimension table) and Table 2 contains mutiple products along with costid (Fact Table).

Table 1 has the cost info like this

CostId	Costsaving
1	12.5
2	25
3	35
4	50
5	74

Table 2 (Fact) has the products

CostID	Product	ID
1	A	10
1	B	11
2	A	12
2	C	13
3	C	14
3	A	15
3	B	16
4	A	17
4	A	18
4	A	19
4	A	20
5	A	21
5	C	22
5	A	23

I wanted to create a measure to calcualate the sum of cost of all products. Table 1 and Table 2 has one-many relationship using CostID. I need to get the final result as 602 which is nothing sum of all costs in Table 2

CostID	Product	ID	Lookup value from Table 1
1	A	10	12.5
1	B	11	12.5
2	A	12	25
2	C	13	25
3	C	14	35
3	A	15	35
3	B	16	35
4	A	17	50
4	A	18	50
4	A	19	50
4	A	20	50
5	A	21	74
5	C	22	74
5	A	23	74
			602

I would appreciate any suggestion on achieving the desired result using DAX measure

Thanks a lot!!

[Anonymous · Accepted solution]

Hi,

Use the below DAX to create the measure:

Total Sum = SUMX( 'Fact', RELATED( 'Dimension'[Costsaving] ) )

'Fact' : Fact Table name
'Dimension' : Dimension Table name

Thanks.

[manojsv]

@Anonymous,

I am getting the following error for the DAX measure mentioned below when i use excel stored locally as a Dim and  (Fact table) uses direct query. but, not getting any error if i imported the fact table. Can you help us in getting resolved this issue when using both the direct query and lookup data from the local file.   I would appreciate any suggestions to acheive the desired result.

"The column 'Dim_ProductCost(ProductCostValue' either doesn't exist or doesn't have a relationship to any table available in the current context."