Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

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

    (Billing)  (Collection)

    related = RELATED(Billing[Key])

     

    Then create such a measure

     

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

     

     

    Best Regards

    Maggie