Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

dynamic calculation based on data table

(Just a note to start with for everyone looking at this topic: it was marked as solved in some way but it isn't solved, so you will not be able to find the solution of the below question in this topi...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anonymous 

    According to youor description, you want to lookup the [Norm pickregels] field to the another table. Right?

    You can click "New Column" to create a calculayed column in your 'CustMst' table:

    Column = LOOKUPVALUE('Norm'[Norm pickregels] , 'Norm'[Customer number],'CustMst'[CustNum])

    The result is as follows:

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Aniya Zhang,

 

This is part of the solution, however I'm still missing the calculation for the required hours.

 

There is a data table called "Norm tabel" in which for each client the number of lines that can be picked per hour is determined. This is so to say the standard number that pickers should be able to do per hour.

 

Now I want to have in the table an additional column that calculates how many hours of work is needed based on this " Norm table" .

 

In the example calculation you shared it would look like the below screenshot.

 

I hope this makes it more clear, if not please let me know.

 

In excel this would be done with like a v-lookup, but I don't know if that can be done in Powerbi.

 

Kind regards,

Roland

 

 

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Anonymous 

In your .pbix file , i cannot find the "Norm tabel".

 Bu for your need , you want to lookup teh value between two tables, you can use the LOOKUPVALUE() in calculated column.

For more information, you can refer to :
LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      Hi, Anonymous 

      According to youor description, you want to lookup the [Norm pickregels] field to the another table. Right?

      You can click "New Column" to create a calculayed column in your 'CustMst' table:

      Column = LOOKUPVALUE('Norm'[Norm pickregels] , 'Norm'[Customer number],'CustMst'[CustNum])

      The result is as follows:

      Best Regards,

      Aniya Zhang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hello Aniya Zhang,

         

        This creates a static column in the data, however I need a dynamic calculation in the dashboard as an additional column added to the measure & table you provided earlier.

         

        so the result will be:

         

        1. Client 2. Number of lines to pick3. Lookup value of norm table based on client4. colum 2 divided by column 3
        = customer description= the measure you already provided earlier in a reply= a dynamic lookup of the norm table based on the client in the first column= a calculation of the result of column 2 divided by resultof column 3

         

         

        I hope this is more clear and that you can help me with this.

         

        Kind regards,

        Roland