I have one table named InvoiceLines in SQL Server which has the following records.
InvoiceID ProductDescription Quantity UnitRate
1 Tow Fee 2.00 217.39
1 Tow Fee 1.00 110.00
In my Canvas Power app(showing one label), I want a multiplication of Quantity and UnitRate and then a sum of all records that have InvoiceID as 1. So, in the text property, I have added this code.
LookUp(InvoiceLines, ThisItem.InvoiceID = InvoiceID,Sum(Quantity*UnitRate))
Where ThisItem.InvoiceID is 1. But it gives the result as 434.78. It took only the first records into consideration not second one. I want the result as 544.78. How can I do that in canvas app?
Lookup only gives back the first found result. User filter instead. Sum(Filter(Table, ColumnName = DesiredValue), Quantity * Unit Rate)