cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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.

 

datamodel.png

 
 

 

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.

2020-04-25_9-28-52.png


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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User IV
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User II
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


Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

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.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors