cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajsjoshua Regular Visitor
Regular Visitor

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]

 

 

Regards,

Joshua.

 

 

6 REPLIES 6
Super User
Super User

Re: Measures based on Lookup

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ajsjoshua Regular Visitor
Regular Visitor

Re: Measures based on Lookup

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

Highlighted
Super User
Super User

Re: Measures based on Lookup

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ajsjoshua Regular Visitor
Regular Visitor

Re: Measures based on Lookup

Dear Ross,

 

Wow will it works by creating the measure sum(CollectionAmount)in Billing table?

Super User
Super User

Re: Measures based on Lookup

You could actually get away without using the measure at all and just putting the field inside the visual and then creating the filters or context from the billing table on your report.

 

If you do create it as a measure, the measures home table should be the same table as the field you are summarizing.  The filters, which are sitting in the Billing table, are what will make use of the table relationships to give you the correct answer, based on whatever context you pass.


For example, if your billing table has a "City" column and you want a sum for that city.  When you use the city as your filter context, the table relationship will say "Ok, what records in the billing table do we have?  Ok from those records, what records are linked in the collection table?  Ok now we have a short list for the collection table, lets run the measure.  This will sum the values in the amount column based on this filter context.  Ok that is answer X".

Then when you change the filter to another city, it will do all of that again and give you answer Y.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Community Support Team
Community Support Team

Re: Measures based on Lookup

Hi @ajsjoshua

For these two tables, you need to create relationships between them, then create a column in the Collection table.

7.png(Billing)  6.png(Collection)

related = RELATED(Billing[Key]) 

8.png 

Then create such a measure

 

Measure = CALCULATE(SUM(Collection[Amount]),FILTER(Collection,Collection[Key]=Collection[related]))

 

 

Best Regards

Maggie