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...
sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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'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%