dynamic calculation based on data table

[Anonymous]

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

[Anonymous]

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]

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

[Anonymous]

Hello Aniya Zhang,

I'm struggling still a little with this, could you help me to set it up? I have added the norm file to the following link.

https://www.dropbox.com/scl/fi/d9p7vq08j4suaqlu7cxq1/Pickregel-Norm.xlsx?dl=0&rlkey=30qu9iscoaybgq8a...

Kind regards,

Roland

[v-yueyunzh-msft · Accepted solution]

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]

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 pick	3. Lookup value of norm table based on client	4. 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

[Anonymous]

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:

Client	Rows to pick	Lines per hour	Hours
Dopper	21	42	=21/42 = 0,5
Pom	7	125	=7/125 = 0,056
Prospect	7	35	=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

[v-yueyunzh-msft]

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