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

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




Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors