Filter sum by another table

[ARob198]

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.

Thank you very much.

Example:

Table 1
Date	Name	Value
1/31/2016	AB	10
2/29/2016	AB	11
3/31/2016	AB	12
1/31/2016	AA	3
2/29/2016	AA	4
3/31/2016	AA	5
1/31/2016	AC	2.5
2/29/2016	AC	3.5
3/31/2016	AC	4.5

Table 2
Name	Type
AB	Cash
AA	Stock
AC	Cash

Desired Result
Date	Name	Value
1/31/2016	Cash	12.5
2/29/2016	AB	14.5
3/31/2016	AB	16.5

[v-lili6-msft]

hi  @ARob198 

You could try this way as below:

Step1:

Create a relationship between two tables by Name

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Step2:

Then just create a simple meausre 

Measure = SUM('Table 1'[Value])

Step3:

Now drag type into a slicer to filter data as below:

here is sample pbix file, please try it.

Regards,

Lin

[ARob198]

Hello v-lili6-msft,  I apologzie but this isn't what I need.  Or maybe I don't understand how it can be used to solve the issue.  Perhaps I am not explaining this correctly.  The tables are already related.  I need to use the filtered value as an input in another calculation- I do not want to see it in a table- so it cannot just be a sum using a filter in a visual.  I need the cash value as an input for other calculations.  What is the formula for a sum that is filtered by a criteria in another table?  Just using Sum(value) does not give me a filtered value to use in another calculation.

Thank you

[v-lili6-msft · Accepted solution]

hi  @ARob198 

Do you mean that you do a filter in visual level filter and then use the measure result in another Measure?

If so, you need to add the same filter into that visual too, the measure result in based on it row context and filter context.

otherwise, you could define it in the measure like this

Measure =CALCULATE( SUM('Table 1'[Value]),'Table 2'[Type]="Cash")

But this will not interaction by slicer.

Regards,

Lin

[Anonymous]

@ARob198 Your desired result is wrong because the solution provided matches that criteria. Please re-evaluate what your desired result is.

[ARob198]

toolatejake

Can you be more specific?  sum([value]) does not solve my problem without adding a filter and I am having problems creating one inside a measure.  If you are unable to provide more help or an explaination or you are unwilling to provide more detail, please allow someone else to repond.

[Anonymous]

@ARob198I think we are talking past each other here. What I meant was, the problem as you had laid it out was solved in the solution by @v-lili6-msft . However, you are saying that is incorrect. I was trying to simply propose that you reframe the problem more specifically so members of this forum can help you.

As an aside, cross table filters are based on the relationships of the tables, and those relationships are enforced inside measures, unless you modify them the filter with a formula like ALL, SELECTED, and so on. You can find a filter function overview here: https://docs.microsoft.com/en-us/dax/filter-functions-dax

[ARob198]

Hi

toolatejake

As I explained when I replied to

@v-lili6-msft

, I cannot have the solution in a table.  I need a measure so that I can use it for other calculations.  Do you know how to solve this as a measure with a filter, without creating another talbe?  Otherwise, please allow me to repost to allow someone else to help me.

[Anonymous]

Is this a running total? If so, you can use the Quick Measure in PowerBI. https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/

If it is not a running total, you should just use a measure with SUM([value]).

You probably need a date dimension too though if this is accounting data.

[ARob198]

I am not really sure what you mean. I am trying the formula below. I am not getting an error, but the end result is blank. What do you suggest? Thank you for your help.

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