cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Get Sum Of Values In Dimension in Filter context of Fact

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Get Sum Of Values In Dimension in Filter context of Fact

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User IV

## Re: Get Sum Of Values In Dimension in Filter context of Fact

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

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User II

## Re: Get Sum Of Values In Dimension in Filter context of Fact

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

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.