Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
Solved! Go to 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])
)
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.
Hello,
THanks for helping.
The Total looks wrong
Hi @Laurent123
I updated my measure formula and it now shows correctly
Total Price =
SUMX(
'Table 2',
'Table 2'[Qty] * RELATED('Table 3'[Price])
)
Hello, thank you.
May I ask you how did you create the column "Sum of Column"?
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
Sorry but I don't have all those choices
It is in French but ...
User | Count |
---|---|
13 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
28 | |
19 | |
13 | |
11 | |
7 |