Get Sum Of Values In Dimension in Filter context of Fact

[jptak]

I have a standard star schema data model with a Fact table that contains a daily snapshot of product inventory by Branch (store), Equipment ID and of course date. Data model is shown below. Workign primarily with the 2 tables outlined in red.

In the DimEquipment table, there is an attribute that contains our purchase price paid for that asset. You can't see it in the screenshot above, so you could use CurrentDepreciation if that makes things easier.

What I thought I could do is create a measure in the fact table called Original Equipment Cost that would be the sum of the purchase prices for each EquipmentID in the Fact table for the given date and branch.

I need to show this data on a line chart by day as well as run some other calculations on the total.

If I just do a simple measure in the Fact table as SUM('DimEquipment'[Cost]), I get the total for all records in the dimension with no changes based on slicer context.

I am struggling with the appropriate DAX formula to calculate this in context of filters/slicers on the page.

I have a hunch that its a DAX formula with CALCULATE, SUM, FILTER, but I am just not familar enough with DAX yet to understand how to craft it.

Any help would really be appreciated.

[kentyler]

So, just to be clear. You want to take a record in Dim Equipment...and SUM costs in Dim Inventory ?

[Greg_Deckler · Accepted solution]

Right, so switch your relationship direction between those tables to "Both"