cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manojsv Frequent Visitor
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

 

CostIdCostsaving
112.5
225
335
450
574

 

Table 2 (Fact) has the products

 

CostIDProductID
1A10
1B11
2A12
2C13
3C14
3A15
3B16
4A17
4A18
4A19
4A20
5A21
5C22
5A23

 

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

 

CostIDProductIDLookup value from Table 1
1A1012.5
1B1112.5
2A1225
2C1325
3C1435
3A1535
3B1635
4A1750
4A1850
4A1950
4A2050
5A2174
5C2274
5A2374
   602

 

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

 

Thanks a lot!!

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
vaibhavdesai Established Member
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.

View solution in original post

2 REPLIES 2
vaibhavdesai Established Member
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.

View solution in original post

manojsv Frequent Visitor
Frequent Visitor

Re: DAX to calculate sum based on another table

@vaibhavdesai,

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

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!

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

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