Forum Discussion

maurizio75's avatar
maurizio75
Frequent Visitor
6 years ago

Power pivot - calculate formula

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 o...
maurizio75's avatar
maurizio75
Frequent Visitor
6 years ago

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. 

 

StageBlockValveSystemFert TankTypeRow space (m)Plant space (m)Plant numberAgeFlow (l/hr)Area (ha)
STAGE 3[S3]A[S3] AFullerStage 3E-HB30.9238116  
STAGE 3[S3]BBot[S3] B BottomFullerStage 3E-HB30.9427616  
STAGE 3[S3]BTop[S3] B TopFullerStage 3E-HB30.9263612  
STAGE 3[S3]F[S3] FFullerStage 3E-HB30.922769  
STAGE 3[S3]H[S3] H BottomFullerStage 3E-HB30.927875  
STAGE 3[S3]H[S3] H TopFullerStage 3E-HB30.927875  

 

 

BlockShiftDateStart timeEnd timeIrrigation Length (hrs) 1Fert Restrictor (L/hr) 1Fert mix type
[S3] A32/12/201912:0012:400.6780Fert Main Farm June2017
[S3] B Bottom32/12/201912:0012:400.6780Fert Main Farm June2017
[S3] H Bottom52/12/201910:5211:300.6380Fert Main Farm June2017
[S3] H Top52/12/201910:5211:300.6380Fert Main Farm June2017
[S3] A34/12/201913:4014:380.97 Water
[S3] B Bottom34/12/201912:4013:401.00 Water
[S3] B Top44/12/201912:4013:401.00 Water
[S3] F44/12/201911:0011:430.72 Water
[S3] H Bottom54/12/201914:3915:350.93 Water
[S3] H Top54/12/201914:3915:350.93 Water
[S3] B Top46/12/201910:4011:100.50 Water
[S3] F46/12/201910:4011:100.50 Water
[S3] H Bottom56/12/201910:1010:400.50 Water
[S3] H Top56/12/201910:1010:400.50 Water
[S3] A310/12/201912:4013:100.50 Water
[S3] B Bottom310/12/201912:4013:100.50 Water
[S3] H Bottom510/12/201911:4012:100.50 Water
[S3] H Top510/12/201911:4012:100.50 Water
[S3] A311/12/201911:1511:450.50 Water

 

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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's avatar
    maurizio75
    Frequent Visitor
    6 years ago

    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   MarAprMay
    SystemValve6789   
    Fuller[S3] A2.23%2.23%2.23%2.23%2.23%2.23%2.23%
     [S3] B Bottom4.12%4.12%4.12%4.12%4.12%4.12%4.12%
     [S3] B Top3.70%3.70%3.70%3.70%3.70%3.70%3.70%
     [S3] F3.20%3.20%3.20%3.20%3.20%3.20%3.20%
     [S3] G7.14%7.14%7.14%7.14%7.14%7.14%7.14%
     [S3] H Bottom2.26%2.26%2.26%2.26%2.26%2.26%2.26%
     [S3] H Top2.37%2.37%2.37%2.37%2.37%2.37%2.37%
     [S3] I8.93%8.93%8.93%8.93%8.93%8.93%8.93%
     [S3] L3.58%3.58%3.58%3.58%3.58%3.58%3.58%
     [S3] R15.29%5.29%5.29%5.29%5.29%5.29%5.29%
     [S3] R26.94%6.94%6.94%6.94%6.94%6.94%6.94%
     [S3] R36.11%6.11%6.11%6.11%6.11%6.11%6.11%
     [S3] U Bottom6.16%6.16%6.16%6.16%6.16%6.16%6.16%
     [S3] U Top3.13%3.13%3.13%3.13%3.13%3.13%3.13%
     [S3] V Bottom5.90%5.90%5.90%5.90%5.90%5.90%5.90%
     [S3] V Top3.61%3.61%3.61%3.61%3.61%3.61%3.61%
     [S3] X6.86%6.86%6.86%6.86%6.86%6.86%6.86%
     [S3] Y6.24%6.24%6.24%6.24%6.24%6.24%6.24%