Forum Discussion
Power pivot - calculate formula
Resident RockstarHi 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
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?
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%
