cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX to calculate sum based on another table

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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: DAX to calculate sum based on another table

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.

2 REPLIES 2
Established Member

## Re: DAX to calculate sum based on another table

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.

Frequent Visitor

## Re: DAX to calculate sum based on another table

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."

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors