Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
9 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
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 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
9 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
    9 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
      9 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].

       

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

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