Average Gross Profit Margins for each Customer

[toodoi]

Hello, I want to be able to see what the average gross profit margin is for a given customer and also potentially for aggregated customers but split by time.

The problem is that different products have different profit margins and different customers buy varying quantities of the products. This means you can't just take an average of all the profit margins for products that customers are buying because the quantities they are buying affects the total average profit margin.

It would need to be an equation like [Gross margin]/[Standard price] to find the gross profit margin for a given product and then conditional statements to multiply that margin by the invoice amounts in the Sales table which have matching Product Codes. To find the average margin for a customer, it would need to be multiplied not by the nominal invoice amount but by each invoice's proportion of a customer's total invoices for a given period to find the average. 

I am new to Qlik Sense and have spun my wheels for a day trying to acomplish this with no luck. Any help would be appreciated.

I have a Product table and sales table captured below. These tables are linked by the Product Code (called Product in 2nd table).

Product

Sales

This is how I've tried to do it:

Sum(([Gross margin]/[Standard price]*100)*[Invoice amount]/Sum([Invoice amount]))

It doesn't let me because there are two aggregate functions and I cant get the aggr() to work. But basically it should calculate (gross margin/standard price)*100 on an element-wise basis across an array and then multiply it element-wise with (invoice amount/Sum(invoice amount)) to get the relative gross profit margin for each sale which I'm not sure if thats possible in Qlik. Then it should sum these to find the total average.