Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
7 years ago
Solved

HELP !! Sum formula with different variables

Hey Guys,

 

I need a formula that can calculate the following.

 

I have a table 'Lengtes' that has the average lengte in a certain area devided between 'Kern', 'Industrieel' and 'Buitengebied'

For example

 

 

What i need to calculate is that if an adress has been completed (Another Table 'Adressen' and Column 'Civiel gesloten' <> Null) it looks at the type of adres for example 'Buitengebied' and then check in which 'DP naam' it is for example STAS_1E_DP007 then its 204,0 meters

So if we complete 100 adresses we get the total meters that were digged based on different DP names and different averages

 

Im hoping someone can help me out here 

 

Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    the join between tables is active, 1:many and with single direction?
    you added the new column to the Adressen table, correct?

    EDIT - maybe there are some spaces/non printable characters in the Gebiedstype column?  try this code

    Average =
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 ()
    )
)

     

     

9 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    is there any join between the 2 tables?
    can you post here sample rows from both tables that can be copied?

    • RvdHeijden's avatar
      RvdHeijden
      Post Prodigy
      7 years ago

      Hey Stachu, there is a relationship between the two tables based on 'DP naam'.

      Here is an excel copy/paste from the excel

       

      So basically if an adress has been 'Civiel gesloten' (<> Null) then it should look at the DP naam and Gebiedstype and check in the tabel 'Lengths' what the average length is for that DP name and Gebiedtype

       

      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
      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        this is syntax for calculated column

        Column = 
IF(Adressen[Civiel gesloten]<>BLANK(),
    SWITCH(Adressen[Gebiedstype],
        "Kern",RELATED(Lengtes[Average length Kern]),
        "Buitengebied", RELATED(Lengtes[Average length Buitengebied]),
        "Industrieel", RELATED(Lengtes[Average length Industrieel]),
        BLANK()
    )
)