Recalculate SUM in multiple dimensions on row level

[Anonymous]

Dears,

I need help regarding reporting according to target execution. I am trying to set up table by client and his target execution for each producer. Target is measured as sales.

I have 4 tables described below (with relationships)

1. tClients

client_id

2. tTarget

client_id, producer, target

3. tSales

client_id, product_id, quantity

4. tProducts

product_id, producer, price

Relationships

tClients.client_id 1:* tTarget.client_id

tClients.client_id 1:* tSales.client_id

tProducts.product_id 1:* tSales.product_id

Table I with results I need to create:

Client_id | Producer | Sales Value | Target | Target Execution

Sales Value = quantity * price (summarized per first client then producer and calculated on product level)

Target = each Client has different Target per Producer

Target execution = Sales Value / Target

Can anyone help me creating formulas for Sales Value, Target, Target Execution? Should it be done by measures or additional columns in my datasets?

Much appreciated,

Kind Regards

[amitchandak]

what is producer here in the target.  why it does have a role in sales. (Why it is not present in sales and present only in Target)

This calculation should be a column

Sales Value = quantity * price (summarized per the first client then producer and calculated on product level)

Multiplication should be at line level

Column means = A*B and then use as sum(A*B)

the measure means Sum(A) * Sum(b)

[Anonymous]

It means that client has to buy certain Value of Products from Producer X.

I would imagine it like below:

Client	Producer	Sales Value	Target	Target Execution
1	X	150	200	75%
1	Y	100	100	100%
1	Z	200	150	125%
2	X	250	250	100%
2	Y	300	200	150%
3	X	450	500	90%

Does that explain it properly?