Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost 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' F...
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 ()
    )
)

     

     

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity 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
Icon for Post Prodigy rankPost 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
    Icon for Community Champion rankCommunity 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()
    )
)
    • RvdHeijden's avatar
      RvdHeijden
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      Hey Stachu thanks for the help so far but it doesn't work yet

       

      Ive copied the formula and altered it a bit to fit the columnames etc

       

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

       

      The column however doesn't return any values, there isn't an error on it so that is good but it still doesn't return values

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        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 ()
    )
)