Sorting data from 3 tables.

[Laurent123]

Hello,

I have 3 tables.

Table A where I have my work orders.

Work order

Work order 1

Work order 2

Work order3

Table B with all the parts used during those work orders and the quantity. Table A and B are linked with a one-to-many relationship, so a workorder can have multiple references used.

Work order	Reference	Qty
Work order 1	123	5
Work order1	456	8
Work order 1	789	7
Work order 2	789	9
Work order 2	456	3
Work order 3	789	8

Table C with the references and the price for each

Reference	Price
123	200
456	300
789	400

I would like to choose a work order and see all the used parts with the total price per line and a Grand Total.

I’m starting with Power BI but have worked since years with access and Excel.

If someone could help that would be wonderful.

Thank you so much and have a great 2024 year.

[Dangar332 · Accepted solution]

Hi, @Laurent123 

you can download file click HERE 

[v-jialongy-msft]

Hi @Laurent123 

Here are the three tables you provided, The relationship between them has been properly established

Based on your three tables, you can build such a table in the Report View interface

You can create such a measure to calculate  total price per line

Total Price = MAX('Table 2'[Qty])*MAX('Table 3'[Price])

Then you can put this measure into the table

Then you can create a slicer and drag the Work order field into the slicer

This is the result you want, each time you select an option in the slicer, the values in the table will change as well

^{Best Regards,}

^Jayleny

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

[Laurent123]

Hello,

THanks for helping.

The Total looks wrong

[v-jialongy-msft · Accepted solution]

 Hi @Laurent123 

I updated my measure formula and it now shows correctly

Total Price =

SUMX(

    'Table 2',

    'Table 2'[Qty] * RELATED('Table 3'[Price])

)

[Dangar332]

hi, @Laurent123 

Column = CALCULATE(MIN('l 3'[price])*MIN('l 2'[qty]))

make a new column 

[Laurent123]

Hello, thank you.

May I ask you how did you create the column "Sum of Column"?

[Dangar332]

hi, @Laurent123 

when you drag your column to table visula or other visual in visualization pane click on drop down sign(show in image below) your column name that open selection box from that you can select (sum,avearge,count....)

look below image

[Laurent123]

Sorry but I don't have all those choices

It is in French but ...

[Dangar332 · Accepted solution]

Hi, @Laurent123 

you can download file click HERE