Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manojsv
Regular 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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.