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