Lookup/addition

[Max01]

Hello,

I wonder if somebody may be able to help me out with the following problem. I have built an app in Power Apps that takes timestamps and logs these against a job number as you can see in the image below:

I would like to create a second table in PowerBI which calculates the following (please not there are no formulas here, I have added manually):

Ultimately I am trying to plot hours worked against job number using the start/end times from the app output. I am not sure how to get PowerBI to lookup the job numbers and then automatically add together all the corresponding hours.

Please can somebody provide me with assistance?

Kind regards,

Max

[Ashish_Mathur]

Hi @Max01,

Try this:

1. Drag Job from the Data table to your table/matrix visual
2. Write this measure

=SUM(Data[Hours])

Hope this helps.

[v-chuncz-msft · Accepted solution]

@Max01,

You may use DAX below to add a calculated table.

Table =
SUMMARIZE ( Table1, Table1[Job], "Hours worked", SUM ( Table1[Hours] ) )

[Max01]

Thank you both for the answers, I was able to create the desired table using the DAX code from @v-chuncz-msft.

However, the next step which I assumed would have been relatively straightforward is elluding me. Each job (job number is unique) will be working on a specific part number. There is another table already in PowerBI which looks similar to this:

Linking the two tables is easy enough. However I wanted PowerBI to show a graph of average job duration for each customer part number, using the example from these posts, it would need to create a table similar to this:

Ultimately we are looking for two things:

1. Average job time for a specific part number
2. Line chart showing job time for a specifc part number vs job end date

I cannot find a way to do the above, the system will only sum the hours and not realise that I'm looking for the second table. Can you help, again?

Kind regards,

Max

[Ashish_Mathur]

Hi @Max01,

Your result is confusing.  In the second table, why is part number 1 appearing twice?

[Max01]

Hi @Ashish_Mathur,

Customers will send us repeat orders, in the table from the exmaple. One job was complete for part number 1 on 20th October and then another job was completed for the customer on 21st October.

I would like to plot the total time to complete a job by customer part number against date. This would allow us to work out the average time to complete a specific part number and therefore the cost of doing so.

Does that make sense?

Kind regards,

Max

[Ashish_Mathur]

Hi,

So it means that in the result Table, there should also be a Customer Name column (which you have not shown and hence my confusion).  Share the link from where i can download your file.

[Max01]

Hi @Ashish_Mathur,

Thank you for taking the time to try and understand my problem, I appreciate I am not doing a very good job at describing it to you and see that I have confused the situation. Perhaps explaining the process and end result may help.

We are an engineering company who are sent parts which we process and send back. Sometimes the parts come to us via sub contractors of the OEM (original equipment manufacturure). This means that the same part can be sent to us for processing by many different companies. Each time a part is sent for processesing it is assigned a unique job number. It can sometimes take days to process a part.

We take a time stamp every time we start and stop working on a part so that we can calculate how long each job takes. We would like to calcualte the average time it takes to process each part.

The app records the time stamps when the factory operators start and stop working on the job which can give us the total time per job. We have a table which records which part number is worked on in each job. The job number is unique but the part number can be worked on multiple times as we receive repeat orders.

We have all the raw information in a similar format to the tables provided before and we are looking to plot total job time by part against time. Hopefully over time we will see that the time per part decreases as we increase our efficiency.

Does that make a little more sense now?

Kind regards,

Max

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Max01]

Hi @Ashish_Mathur,

Thank you by setting out all the data in excel I was able to figure out how to arrange it in PowerBI to get the result I was after, thank you for your help.

Max