Associating a cost to a quantity of items

[ProverbialPaul]

Hello all

I have a table that tracks the amount of parts that have been replaced on an item entered into a Sharepoint list through a Powerapp.

The table in PowerBI looks as follows:

I need to work out how to associate a cost to the parts that have been replaced but I don't know how to do it.

I know the cost of each part replaced but that information isn't currently recorded in the above table.

I can make a separate table with associated cost, like this:

But I don't know how to associate one with the other as there's no associated relationship.

Hoping someone can help.

Thank you

[ProverbialPaul]

Thanks Gents.

Now I think about it, that SP list hasn't been built correctly has it.

Each entry should have it's own line with the repair type, duration and qty being associated to a line. That way a cost can be associated and calculated more easily.

Thanks for your help, I really appreciate it.

[sturlaws · Accepted solution]

Hi, @ProverbialPaul,

I would recomend to pivot this table using power query to make the table look like this:
Date|Start_time|finish_time|Module_number|RepairType

Then you will have to tables which are easibly related. 

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

Or create a measure for each column, 

e.g

measure_drawers_replaced =
SUM ( [drawers replaced] ) * LOOKUPVALUE ( [Cost], [Part], "Drawers replaced" )

 
Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly

[hnguy71]

Hi @ProverbialPaul ,

You would need to transform your columns to rows and then create a many-to-one relationship: