Forum Discussion
Filter sum by another table
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 |
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
9 Replies
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
Hello , 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
ARob198 Your desired result is wrong because the solution provided matches that criteria. Please re-evaluate what your desired result is.
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.
- 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])))
