HELP !! Sum formula with different variables

[RvdHeijden]

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 

[Stachu]

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

[RvdHeijden]

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'

Straatnaam	Huisnummer	Toevoeging	Civiel gesloten	DP naam	Gebiedstype
Zitterstraat	2		24-9-2018	STAS_1E_DP035	Kern
Zitterstraat	4		18-5-2018	STAS_1E_DP037	Kern
Zitterstraat	6	A	14-9-2018	STAS_1E_DP035	Buitengebied
Zitterstraat	8		2-8-2018	STAS_1E_DP035	Industrieel
Zitterstraat	10		4-9-2018	STAS_1E_DP036	Industrieel
Zitterstraat	12			STAS_1E_DP037	Buitengebied
Zitterstraat	14			STAS_1E_DP037	Kern
Zitterstraat	16		8-8-2018	STAS_1E_DP040	Kern

Tabel 'Lengths'

DP naam	Average length Kern	Average length Buitengebied	Average length Industrieel
STAS_1E_DP035	56,0	124,6	89,5
STAS_1E_DP036	30,0	63,4	48,6
STAS_1E_DP037	29,5	89,6	74,9
STAS_1E_DP040	14,9	111,4	65,0

[Stachu]

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]

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 · Accepted solution]

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]

@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]

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]

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]

@RvdHeijden glad to help 🙂