How to calculate total revenue for selected company using third table reference

[saifullahkhan]

Hope everyone doing well, i am facing problem in referencing, I have these tables:

Tables

1. COMPANY (COMPANY_ID, company name)
2. COMPANY_SOLD_PRODUCTS (SOLD_ID, COMPANY, product, price )
3. CALCULATE_COMPANY_REVENUE (REVENUE_ID, COMPANY, total)

Now how would we find the total price for each company, I want the user to select the company from dropdown list and we should see the total price.

Example:

total = SUM(SELECT(COMPANY_SOLD_PRODUCTS[price], ([_Thisrow].[COMPANY] = COMPANY_SOLD_PRODUCTS[COMPANY])))

But because there is no direct link from CALCULATE_COMPANY_REVENUE to COMPANY_SOLD_PRODUCTS so we can't use this formula, it gives an error: Cannot compare list with reference.

Is there any way to solve this problem?

THANKS IN ADVANCE

[Marc_Dillon]

https://community.appsheet.com/t/referencing-a-column-value-in-a-table-versus-referencing-all-column...

[saifullahkhan]

Thanks, let me check.

[saifullahkhan]

Not working, still same error: Cannot compare list with reference