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

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
Icon for Community Champion rankCommunity 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?

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

    Hey Stachu, your formula did work properly.

    It was just i had 2 columnnames 'DP gebied' and 'DP gebied name' and the relationship was based on the wrong column

     

    I changed it and now it returns the correct values

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    RvdHeijden glad to help :)