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' 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
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()
    )
)
RvdHeijden's avatar
RvdHeijden
Post 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
    Community 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
      Post 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

       

       

      • Stachu's avatar
        Stachu
        Community Champion
        7 years ago

        if Gebiedstypes is other than the specified values it returns blank, it's the last formula within SWITCH
        code works fine based on the data you sent, not sure where the issue is

        Is the join working properly? if you create visual with dimensions from Adressen and Lengtes it behaves as expected?