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
ARob198
Helper IV
Helper IV

Create a measure which sums values based on a filter in another table

Hello!  I have a problem that seems like it should be easy, but I can't seem to get the formula right. 

 

I have two tables.  One has values by asset by date, the second defines the type of asset.  I need to create a measure or calculated column which sums the value by date and by type to use the values for additional analysis.  The tables are related by Name.  I am not sure how to filter the sum of the values by the Type.  I do not want to see this in a table.  I need to create a measure that sums by the filter so that I can use the value in other calculations.  The result table below is only to show you an example of what the measure will need produce if I decided to put it into a table.  I want to SUM the VALUE in Table 1 filtered by the TYPE in Table 2.  So for example, I want the sum of cash for 1/31, 2/29, and 3/31.  I have been trying to use the following formula, it does not error out in Dax, but it also does not product a result.

 

Calculate(SUM('Table1'[VALUE]), filter(ALL(Table2), (Table2[TYPE] = "Cash"&& 'Table1'[NAME DETAIL] = 'Table2'[NAME])))

 

Thank you very much for your help.

 

Example:

Table 1  
DateNameValue
1/31/2016AB10
2/29/2016AB11
3/31/2016AB12
1/31/2016AA3
2/29/2016AA4
3/31/2016AA5
1/31/2016AC2.5
2/29/2016AC3.5
3/31/2016AC4.5

 

Table 2 
NameType
ABCash
AAStock
ACCash

 

Example only; Desired Result will be a measure, not a table  
DateNameValue
1/31/2016Cash12.5
2/29/2016Cash14.5
3/31/2016Cash16.5
2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

If you already have the relationship, then all you should need to do is to restrict the Type to Cash.

 

SumCash = CALCULATE ( SUM ( Table1[Value] ), Table2[Type] = "Cash" )

View solution in original post

v-yiruan-msft
Community Support
Community Support

Hi @ARob198 ,

You can create a measure as below on the premise that Table 1 and Table 2 have created a relationship based on Name field:

SumofValue = CALCULATE(SUM('Table1'[Value]),ALLEXCEPT('Table2',Table2[Type]))

sum of value.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @ARob198 ,

You can create a measure as below on the premise that Table 1 and Table 2 have created a relationship based on Name field:

SumofValue = CALCULATE(SUM('Table1'[Value]),ALLEXCEPT('Table2',Table2[Type]))

sum of value.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlexisOlson
Super User
Super User

If you already have the relationship, then all you should need to do is to restrict the Type to Cash.

 

SumCash = CALCULATE ( SUM ( Table1[Value] ), Table2[Type] = "Cash" )

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.