Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
8 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
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi RvdHeijden,

 

You need multiple columns. So is it necessary to use only one formula?

Seems you already have the formulas. Can you share the original data and the expected result?

 

 

Best Regards,
Dale

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
8 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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi RvdHeijden,

     

    How does the table 'Schades' connect to others? For example, the damage of [[Avra] Ploeg 1]  is 1. But how can we get the total production?

     

     

    Best Regards,
    Dale

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

      v-jiascu-msft thanks for helping

       

      This is the formula that calculates the production

       

      Gegraven m1 (productie) =
      IF (
      Adressen[civiel gesloten] <> BLANK ();
      SWITCH (
      TRIM ( Adressen[Gebiedstype] );
      "Kern"; RELATED ( Lengtes[Gemiddelde graaflengte Kern] );
      "Buitengebied"; RELATED ( Lengtes[Gemiddelde graaflengte Buitengebied] );
      "Industrieel"; RELATED ( Lengtes[Gemiddelde graaflengte Industrieel] );
      BLANK ()
      )
      )

       

      It refers to the Adress tabel with 'related' and here is a picture with the relationships

      the lengths come out of the 'Lengtes' table so there is a relationship between them