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
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi RvdHeijden

 

Can you share some data and the acutal output expected.

 

Cheers

 

CheenuSing

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