Power pivot - calculate formula

[maurizio75]

I have two table connected each other thorugh the block/valve name; table 1 represent a list of blocks with fixed information which are not changing over the time; table 2 represent the records for our watering and fertigation for every block. 

Considering that the irrigation occurs on few blocks at the time, so they belong to the same shift; I am trying to generate a powerpivot table reporting for each block/valve (rows) and week by week (coloumn) the ration between the flow rate specifc to each block and the total flow rate for all the blocks irrigated under the same shift for each week, The formula I am using is the following but the filter is not working for me.

sum(Blkdtls[Flow (l/hr)])/CALCULATE(sum(Blkdtls[Flow (l/hr)]),all(Blkdtls[Valve]),FILTER(Records,average(Records[Shift])))

Can anyone help me, please?

Table 1

ValveSystemFert TankTypeRow space (m)Plant space (m)Plant numberAgeFlow (l/hr)

[Featherstones] F01	Featherstone	Fstones	RE	3	1.2	3174	5	24952
[Featherstones] F02	Featherstone	Fstones	RE	3	1.2	3539	5	27560
[Featherstones] F03	Featherstone	Fstones	RE	3	1.2	3409	5	26648
[Featherstones] F04	Featherstone	Fstones	RE	3	1.2	3379	5	26704
[Featherstones] F05	Featherstone	Fstones	E-HB	3	0.9	4523	5	28104
[Featherstones] F11	Featherstone	Fstones	E-HB	3	0.9	10435	4	23058

Table2

Block                         Shift            Date      Start timeEnd time Irrigation Length (hrs) 1Fert Restrictor (L/hr) 1

[Grays Rd] GR01	1		7/02/2019	8:00	8:45	0.75	7.6
[Grays Rd] GR02	2		7/02/2019	8:00	8:45	0.75	11.3
[Grays Rd] GR03	3		7/02/2019	8:00	8:45	0.75	8
[Grays Rd] GR04	4		7/02/2019	8:00	8:45	0.75	4.4
[Grays Rd] GR05	5		7/02/2019	8:00	8:45	0.75	13.4
[Grays Rd] GR06	0		7/02/2019	8:00	8:45	0.75	14.3
[Grays Rd] GR07	0		7/02/2019	8:00	8:45	0.75	14.8
[Grays Rd] GR07	0		7/02/2019	8:00	8:30	0.50
[Grays Rd] GR08	0		7/02/2019	8:00	8:30	0.50
[Grays Rd] GR12	12		7/02/2019	8:00	8:30	0.50
[Grays Rd] GR15	15		7/02/2019	8:00	8:30	0.50
[Featherstones] F13	5		7/02/2019	8:00	8:30	0.50	20
[Featherstones] F16	5		7/02/2019	8:00	8:30	0.50	20

[sturlaws]

Hi @maurizio75,

I tried to create a report based on the sample data you have provided, but I'm having a hard time figuring out how to split the header line of table 1 correctly. In addition, the valve values of table one don't exist in the block values of, and vice versa.

Perhaps you could create a sample report replicating your issue and share it. Upload it to dropbox/onedrive/drive/other, and share the link.

Cheers,
Sturla

[maurizio75]

Hi @sturlaws ,

thanks for your reply and advice. 

Below I have attached better tables examples for you to play with:

Link "Valve" coloumn of the first table to "Block" coloumn of the second table. 

Stage	Block	Valve	System	Fert Tank	Type	Row space (m)	Plant space (m)	Plant number	Age	Flow (l/hr)	Area (ha)
STAGE 3	[S3]A	[S3] A	Fuller	Stage 3	E-HB	3	0.9	2381	16
STAGE 3	[S3]BBot	[S3] B Bottom	Fuller	Stage 3	E-HB	3	0.9	4276	16
STAGE 3	[S3]BTop	[S3] B Top	Fuller	Stage 3	E-HB	3	0.9	2636	12
STAGE 3	[S3]F	[S3] F	Fuller	Stage 3	E-HB	3	0.9	2276	9
STAGE 3	[S3]H	[S3] H Bottom	Fuller	Stage 3	E-HB	3	0.9	2787	5
STAGE 3	[S3]H	[S3] H Top	Fuller	Stage 3	E-HB	3	0.9	2787	5

Block	Shift	Date	Start time	End time	Irrigation Length (hrs) 1	Fert Restrictor (L/hr) 1	Fert mix type
[S3] A	3	2/12/2019	12:00	12:40	0.67	80	Fert Main Farm June2017
[S3] B Bottom	3	2/12/2019	12:00	12:40	0.67	80	Fert Main Farm June2017
[S3] H Bottom	5	2/12/2019	10:52	11:30	0.63	80	Fert Main Farm June2017
[S3] H Top	5	2/12/2019	10:52	11:30	0.63	80	Fert Main Farm June2017
[S3] A	3	4/12/2019	13:40	14:38	0.97		Water
[S3] B Bottom	3	4/12/2019	12:40	13:40	1.00		Water
[S3] B Top	4	4/12/2019	12:40	13:40	1.00		Water
[S3] F	4	4/12/2019	11:00	11:43	0.72		Water
[S3] H Bottom	5	4/12/2019	14:39	15:35	0.93		Water
[S3] H Top	5	4/12/2019	14:39	15:35	0.93		Water
[S3] B Top	4	6/12/2019	10:40	11:10	0.50		Water
[S3] F	4	6/12/2019	10:40	11:10	0.50		Water
[S3] H Bottom	5	6/12/2019	10:10	10:40	0.50		Water
[S3] H Top	5	6/12/2019	10:10	10:40	0.50		Water
[S3] A	3	10/12/2019	12:40	13:10	0.50		Water
[S3] B Bottom	3	10/12/2019	12:40	13:10	0.50		Water
[S3] H Bottom	5	10/12/2019	11:40	12:10	0.50		Water
[S3] H Top	5	10/12/2019	11:40	12:10	0.50		Water
[S3] A	3	11/12/2019	11:15	11:45	0.50		Water

[sturlaws]

alright.

Could you also provide an excel-mockup of how you want your table to look like, with values corresponding to the sample data you have provided?

[maurizio75]

Hi @sturlaws ,

below is the ideal report I am chasing; a power pivot table with week, months and year as coloumn. As rows the different irrigation system and all the valves included in each system.

As value I am chasing the percentage of the flow rate of each valve divided by the total flow rate of each valve under the same shift for the week. Formula I used is below but filters is not doing what I want

sum(Blkdtls[Flow (l/hr)])/CALCULATE(sum(Blkdtls[Flow (l/hr)]),all(Blkdtls[Valve]),FILTERS(Records[Shift]))

		Feb				Mar	Apr	May
System	Valve	6	7	8	9
Fuller	[S3] A	2.23%	2.23%	2.23%	2.23%	2.23%	2.23%	2.23%
	[S3] B Bottom	4.12%	4.12%	4.12%	4.12%	4.12%	4.12%	4.12%
	[S3] B Top	3.70%	3.70%	3.70%	3.70%	3.70%	3.70%	3.70%
	[S3] F	3.20%	3.20%	3.20%	3.20%	3.20%	3.20%	3.20%
	[S3] G	7.14%	7.14%	7.14%	7.14%	7.14%	7.14%	7.14%
	[S3] H Bottom	2.26%	2.26%	2.26%	2.26%	2.26%	2.26%	2.26%
	[S3] H Top	2.37%	2.37%	2.37%	2.37%	2.37%	2.37%	2.37%
	[S3] I	8.93%	8.93%	8.93%	8.93%	8.93%	8.93%	8.93%
	[S3] L	3.58%	3.58%	3.58%	3.58%	3.58%	3.58%	3.58%
	[S3] R1	5.29%	5.29%	5.29%	5.29%	5.29%	5.29%	5.29%
	[S3] R2	6.94%	6.94%	6.94%	6.94%	6.94%	6.94%	6.94%
	[S3] R3	6.11%	6.11%	6.11%	6.11%	6.11%	6.11%	6.11%
	[S3] U Bottom	6.16%	6.16%	6.16%	6.16%	6.16%	6.16%	6.16%
	[S3] U Top	3.13%	3.13%	3.13%	3.13%	3.13%	3.13%	3.13%
	[S3] V Bottom	5.90%	5.90%	5.90%	5.90%	5.90%	5.90%	5.90%
	[S3] V Top	3.61%	3.61%	3.61%	3.61%	3.61%	3.61%	3.61%
	[S3] X	6.86%	6.86%	6.86%	6.86%	6.86%	6.86%	6.86%
	[S3] Y	6.24%	6.24%	6.24%	6.24%	6.24%	6.24%	6.24%