Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maurizio75
Frequent Visitor

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 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] F01FeatherstoneFstonesRE31.23174524952
[Featherstones] F02FeatherstoneFstonesRE31.23539527560
[Featherstones] F03FeatherstoneFstonesRE31.23409526648
[Featherstones] F04FeatherstoneFstonesRE31.23379526704
[Featherstones] F05FeatherstoneFstonesE-HB30.94523528104
[Featherstones] F11FeatherstoneFstonesE-HB30.910435423058

 

 

Table2

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

[Grays Rd] GR011 7/02/20198:008:450.757.6
[Grays Rd] GR022 7/02/20198:008:450.7511.3
[Grays Rd] GR033 7/02/20198:008:450.758
[Grays Rd] GR044 7/02/20198:008:450.754.4
[Grays Rd] GR055 7/02/20198:008:450.7513.4
[Grays Rd] GR060 7/02/20198:008:450.7514.3
[Grays Rd] GR070 7/02/20198:008:450.7514.8
[Grays Rd] GR070 7/02/20198:008:300.50 
[Grays Rd] GR080 7/02/20198:008:300.50 
[Grays Rd] GR1212 7/02/20198:008:300.50 
[Grays Rd] GR1515 7/02/20198:008:300.50 
[Featherstones] F135 7/02/20198:008:300.5020
[Featherstones] F165 7/02/20198:008:300.5020

 

 

4 REPLIES 4
sturlaws
Resident Rockstar
Resident Rockstar

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

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors