4 Replies Latest reply: Apr 14, 2017 1:14 PM by luis lima

# Calculo de Margem

Voltando aqui para pedir ajuda a comunidade.

Tenho duas tabelas, uma com as compras das loja e outra com as vendas das lojas, e queria uma luz em como calcular a margem dos produtos vendidos (Valor Venda - Valor Custo)/Valor Venda

As tabelas seriam assim

TABELA VENDA PARA AS LOJAS

 Data NF Destino Produto Quant Valor Unitario Custo Valor Total Custo 26-Jan-17 27019 Loja 1 BE047 4 60,00 240,00 26-Jan-17 27019 Loja 1 CL001 5 50,00 250,00 26-Jan-17 27019 Loja 1 BE023 4 30,00 120,00 26-Jan-17 27019 Loja 1 CL212 3 80,00 240,00 26-Jan-17 27019 Loja 1 CL012 2 75,00 150,00 26-Jan-17 27019 Loja 1 ML012 7 90,00 630,00 01-Feb-17 27095 Loja 2 BE047 5 60,00 300,00 01-Feb-17 27095 Loja 2 CL001 5 5,00 25,00 01-Feb-17 27095 Loja 2 CL012 2 75,00 150,00

TABELA VENDA DAS LOJAS

 Filial Data Venda NF Produto Quant Valor Unitario Venda Valor Total Venda Loja 2 09-mar-17 4454 CL012 1 168,00 168,00 Loja 2 10-mar-17 4456 BE047 1 128,00 128,00 Loja 2 10-mar-17 4456 CL001 1 105,00 105,00 Loja 1 01-mar-17 14704 BE047 1 128,00 128,00 Loja 1 01-mar-17 14704 CL001 1 105,00 105,00 Loja 1 01-mar-17 14704 CL012 1 168,00 168,00 Loja 1 28-mar-17 14825 CL001 4 105,00 420,00 Loja 1 28-mar-17 14825 ML012 4 199,00 796,00

Luis

• ###### Re: Calculo de Margem

Why not join both the tables and use normal expression (Value Sale - Value Cost) / Value Sale??

• ###### Re: Calculo de Margem

The problem is I just want the margin from the produts sold.

To pick the "Value Sale" is easy, hard is to get the respective (same products and same quantities) "Value cost"

Can you help me?

• ###### Re: Calculo de Margem

When you join with product, you get the Value Sale of same products I didn't get you why you need same quantities??

Can you please explain what you are trying to calculate??

• ###### Re: Calculo de Margem

Shiva,

Let me try to explain better

Another example

Cost Table

 Product Quant Value Cost Total Value Cost Prod 10 10 100,00 1.000,00 Prod 20 5 75,00 375,00

Sold Table

 Product Quant Sold Value Sale Total Value  Sale Prod 10 3 300,00 900,00 Prod 20 1 150,00 150,00

The correct margin for the sold products is

Total Value Sale = 1.050 (3 x 300 + 1 x 150)

Total Value Cost = 375  (3 x 100 + 1 x 75)

Margin = 0,6429 or 64,29%

I tried to do that in 2 ways at quik sense and get different number as bellow

(Sum([Value Sale])-Sum([Value Cost]))/Sum([Value Sale])  -  I get 0,6111 or 61,11%

(Sum([Total Value  Sale])-Sum([Total Value Cost]))/Sum([Total Value  Sale])  -  I get  -0,3095  or  -30,95%

Hope you can help me.

Cheers