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

     

     

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

       

       

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

        Stachu the relationship between Adressen and Lengtes goes both ways and is a Many (adressen) to One (Lengtes)

        Ive added a new calculated column to Adressen en copied your formula.

         

        Ive tried your new formula but it still doesn't return any values.

         

        Does your formula take in account if the column 'gebiedstypes' has blank values ? im sure not all adresses have a Gebiedstype yet