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

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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'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