calculate formula with filter from two different tables

[maurizio75]

Hi, 

I am having problem in creating a calculate formula which has filters from two tables.

I have one tables 'Block' containing information like[Valves], [flow rate l/hr] and another table 'Records' whith information like [Irrigation length] and [ Shift]; and also I have a 'Date' table. 

I am trying to run a power pivot table listing all the valves (rows) and weeks (coloumns) and as values the ratio between the flow rate for each valve and the total flow rate for the valve with same shift.

Division	Valve	Flow rate (l/hr)
West	A	30
West	B	25
West	C	10
West	D	20
East	E	30
East	F	25

Date	Valve	Irrigation lenght	Shift
01/02/2019	A	0.5	1
01/02/2019	B	0.6	1
01/02/2019	A	1	1
02/02/2019	A	2	1
02/02/2019	B	1	1
03/02/2019	C	2	2
03/02/2019	A	1	1
06/02/2019	D	0.5	2
07/02/2019	E	0.5	3
09/02/2019	C	0.5	2

Formula I am using is sum(Blocks[flow rate])*Calculate(sum(Blocks[flow rate]),filters(Recods[Shift],All(Blocks[Valves]))

Thanks in adnvance if anyone can help

[Anonymous]

what output are you expecting?

Please be clear with your requirement.

[maurizio75]

Hi,

my expectation is to have something like that, where Valve run together on the same shift for the week add up to 100% (e.g. week 1 valve A and B are shift 1 and together is 100%, but I don't want the shift number to appear on the table)

Valve   

Valve	Week1	Week 2	Week 3	Week 4
A	60%	60%	60%	35%
B	40%	40%	40%	30%
C	80%	80%	80%	35%
D	20%	20%	20%	100%
E	70%	70%	70%	70%
F	30%	30%	30%	30%

Thanks in advance if you can give me any help