Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
8 years ago

Need help with my formula

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 na...
Need Help
RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
8 years ago

Anonymous

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's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    RvdHeijden
    Icon for Post Prodigy rankPost Prodigy
    8 years ago

    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's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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's avatar
        RvdHeijden
        Icon for Post Prodigy rankPost Prodigy
        8 years ago

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