Need help with my formula

[RvdHeijden]

Goodday,

I need a little help with my formula

Gemiddelde m1 sleuf =

CALCULATE((

LOOKUPVALUE('Werkbegroting civiel'[graaflengte totaal];'Werkbegroting civiel'[DP naam];ADRESSEN[DP gebied naam])/

LOOKUPVALUE('Werkbegroting civiel'[Aantal adressen per DP];'Werkbegroting civiel'[DP naam];ADRESSEN[DP gebied naam])))

I need this formula to calculate the 'Graaflengte totaal' and devide it by the number of houses per group to get an average.

i tried sumx but that didn't work and neither does calculate.

A single value for column 'DP gebied naam' in table 'ADRESSEN' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

in the table Werkbegroting civiel [DP naam] is unique and in ADRESSEN[DP gebied naam] it is not, is that the problem ? if yes then how do i correct it

[CheenuSing]

Hi @RvdHeijden

Can you share some data and the acutal output expected.

Cheers

CheenuSing

[RvdHeijden]

@CheenuSing

Basically the formula should calculate the total amount of meters dug and devide it by the amount of houses in that are area

So ... for example:

The first part of the formula should return the value '1000' (meters)

LOOKUPVALUE('Werkbegroting civiel'[graaflengte totaal];'Werkbegroting civiel'[DP naam];ADRESSEN[DP gebied naam])/

The second part of the formula should return '50' houses

LOOKUPVALUE('Werkbegroting civiel'[Aantal adressen per DP];'Werkbegroting civiel'[DP naam];ADRESSEN[DP gebied naam])))

And those values should be devided .... 1000/50=200

[CheenuSing]

Hi @RvdHeijden

It will be of great help if you can post some sample data of the diffrent tables in the data model.

From your formula, what are you using the lookupvalue for ?

Cheers

CheenuSing

[RvdHeijden]

@CheenuSing

That wil be difficult but i'll try to be as clear as i can

Table 'Adressen' (List all of the adresses in a project) and a DP is a Distribution Point, every DP has about 46 houses on it. Here the adres is unique

this formula is in the table of 'Adressen'

Adres           DP gebied

adres 1         DP01

adres 2         DP01 

adres 3         DP03

adres 4         DP02

adres 5         DP03

Table 'Werkbegroting(civiel) has a list of every DP with some technical data, here the DP is unique

DP               Graaflengte totaal

DP01            892 meter

DP02            456 meter

DP03            1235 meter

So i need a colum in the table 'Adressen' to calculate the average meter per house so if DP03 has a 'Graaflengte totaal' of 1235 meter and DP03 has a total of 45 houses then the formula should calculate 1235/46= an average of 26,85 m1 per house.

[v-huizhn-msft]

Hi @RvdHeijden,

Based on your description, please follow the steps to get expected result.

1. Create a n:1 relationship between 'Adressen' and 'Werkbegroting civiel' by DP gebied(n) and DP(1) like the screenshot shown.



2. In table 'Werkbegroting civiel', create a calculated column to get each project's total houses using the formula.

total hourse = CALCULATE( COUNT(Adressen[Adres]),USERELATIONSHIP(Adressen[DP gebied],'Werkbegroting(civiel)'[DP]))

3. Then create a calculated column to get the average of per hourse.

Average = DIVIDE('Werkbegroting(civiel)'[Graaflengte totaal],'Werkbegroting(civiel)'[total hourse])

Please feel free to ask if you have any other problem.

Best Regards,
Angelia

[RvdHeijden]

@v-huizhn-msft

Thanks for your help but i haven't tried your option yet but i saw you want the average over ALL the houses and that i already have done but it didn't return the correct average.

The formula obviously was accurate but seeing that there can be a big difference in the meters per DP.

A DP (a cluster of Houses) might have 1000 meters and the other DP might have 100 meters so if we calculate an overall average it wouldn't be real.

That is why i want an average per DP is more accurate and that is why i used the Lookupvalue

[v-huizhn-msft]

Hi @RvdHeijden,

I didn't calculate the average over ALL the houses, [total hourse] is a calculated column, which returns each DP's hourses. Please create sample table and list expected result.

Best Regards,
Angelia

[RvdHeijden]

@v-huizhn-msft

you were rigth, my bad 🙂

The first part of the formula went fine, but the second one returned an error

total hourse = CALCULATE( COUNT(Adressen[id]);USERELATIONSHIP(Adressen[DP gebied naam];'Werkbegroting civiel'[DP naam]))

Average = DIVIDE('Werkbegroting civiel'[Graaflengte totaal];'Werkbegroting civiel'[total hourse])

A circular dependency was detected: Werkbegroting civiel[total hourse], Werkbegroting civiel[Average], Werkbegroting civiel[total hourse].

[RvdHeijden]

@v-huizhn-msftAny ideas ? is it the formula or the relationship(s) ?