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 topic)

 

Hello,

 

I'm looking for support on the creation of a table which calculates the requires hours of work based on two sources of data:

1. dynamically calculated number of picklines per customer (in the screenshots you can see the total lines is 125, from which 65 are dedicated to customer called Prospectt, and 53 to a customer called Dopper)

2. the standard of picklines per hour (separate table, see screenshot)

 

I preferably want a table that is dynamic and calculates the number of hours required per customer. In this example the calculation would be 65/35 for Prospectt, and 53/42 for Dopper. See the screenshot for the result.

 

(I want to emphasize that the numbers (65, 54) vary constantly as orders are completed and new ones get in)

 

Is this possible in Powerbi?

 

I've also attached the powerbi file in the link: https://www.udrop.com/7of9/Dashboard_Picking_&_Packing.pbix 

 

 

Standards table:

Outcome:

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

7 Replies

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

    Hi , Anonymous 

     According to your description, you want to show the data in a table.

    You can try this dax:

    Measure = var _t= FILTER( 'ShpProcD' , 'ShpProcD'[QtyCompl]=0 && 'ShpProcD'[QtyToPlan]=0 && 'ShpProcD'[ProcCde] = "PICKING" )
return 
COUNTROWS(_t)

    The result is as follows:

    For the " 65/35" , i don't kown how to calculate the "35" value in your table.

    Can you give us the sample output you want in the form of a table, so that we can help you better.

     

    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 this:

    ClientRows to pickLines per hourHours
    Dopper2142=21/42 = 0,5
    Pom7125=7/125 = 0,056
    Prospect735=7/35 = 0,2
        

     

    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

     

     

  • 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