Forum Discussion
Measures based on Lookup
Dear all, I have two tables Collection and Billing . Collection: Key Amount Billing: Key Now i want to calculate sum(Amount) based on the condition Collection[key]=Billing[key] ...
Create a table relationship between Billing and Collection in the Table Relationship screen. Create a measure that is simply
YourMeasure = SUM('Collection'[Amount])
Now if you create a visual and use Billing columns, you can include that measure and it will calculate correctly in the lookup fashion you have described.
Dear Ross,
I want to use it as a KPI.
I tried like this
CALCULATE (
SUM( 'Collection'[Amount]),
FILTER ( Collection, Collection[key] = Billing[key] )
)but i am getting error
Will still work as a KPI, your table relationship will handle all of the filter contexts. Using the calculate statement with the filter is not doing anything.
Dear Ross,
Wow will it works by creating the measure sum(CollectionAmount)in Billing table?