Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
7 years ago

HELP!! Calculating

hey Guys,   Ik need some help with a formula, ive made a matrix based on a formula that calculates how many meters a certain team has produced based on averages per area which works perfectly.   ...
Need Help
RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
7 years ago

v-jiascu-msft its not necesarry to have one formula.

 

Table 'Adressen'

StraatnaamHuisnummerToevoegingCiviel geslotenDP naamGebiedstype
Zitterstraat 2 24-9-2018STAS_1E_DP035Kern
Zitterstraat 4 18-5-2018STAS_1E_DP037Kern
Zitterstraat 6A14-9-2018STAS_1E_DP035Buitengebied
Zitterstraat 8 2-8-2018STAS_1E_DP035Industrieel
Zitterstraat 10 4-9-2018STAS_1E_DP036Industrieel
Zitterstraat 12  STAS_1E_DP037Buitengebied
Zitterstraat 14  STAS_1E_DP037Kern
Zitterstraat 16 8-8-2018STAS_1E_DP040Kern

 

Tabel 'Lengths'

DP naamAverage length KernAverage length BuitengebiedAverage length Industrieel
STAS_1E_DP03556,0124,689,5
STAS_1E_DP03630,063,448,6
STAS_1E_DP03729,589,674,9
STAS_1E_DP04014,9111,465,0

 

These were the tables on which the formula was based, this way i got the total amount of meters per team based on a certain area and buildingtype.

 

I also have a different Table named 'Schades' were we register the damages that occured during our work.

That table looks like this

 

StreetnameHousenumberAdditionDamaged InfrastructureTeam
Exportstraat7 CAI[Avra] Ploeg 1
Gemertseweg122ACAI[Konax] Ploeg 3
Nullen11 Koper[Konax] Ploeg 3
Peelkant70 Gas[Konax] Ploeg 5
Peelkant112BWater[v. Beers] Ploeg 3

 

So that means for example that [Konax] Ploeg 3 has caused 2 damages (1 time the CAI cable and 1 time Koper kabel)

In the first formula we calculated that [Konax] Ploeg 3  has a production of 2500 m1 and based on the 2 damages they have a damage quote of 2500 / 2 = 1250, so that means they have 1 damage for every 1250 meters.

 

I want that calculation per typ of damage (infrastructure) and per Team but given the relationships between the tables that shouldn't be a probleem

The expected outcome should be something like 

 

Damaged Infrastructure[Avra] Ploeg 1[Konax] Ploeg 3[Konax] Ploeg 5[v. Beers] Ploeg 3
CAI80090015001200
Koper200016005001200
Gas150050012001500
Water3000300025004000
Totaal2200120018002700

 

The values should be the SUM of total amount in Meters devided by the number of damages of a certain Infrastructure and off course an overall average