Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a Bill of Materials table:
Part# | Qty |
PartA | 2 |
PartB | 3 |
I also have a Part Costs table with the part cost each month
Part# | YearMonth | Cost |
PartA | 201901 | 1.25 |
PartA | 201902 | 1.3 |
PartA | 201903 | 1.5 |
PartB | 201901 | 2.6 |
PartB | 201902 | 2.1 |
PartB | 201903 | 2.7 |
I'm trying show the effect of a price change which is very different if the price goes up $1 and you use 1 of them in an item vs going up by a $1 and use 100 of them in a item.
Any ideas?
Hi @robofski ,
"I'm trying show the effect of a price change which is very different if the price goes up $1 and you use 1 of them in an item vs going up by a $1 and use 100 of them in a item."
I don't particularly understand the effect you want to achieve.
Could you describe in more detail the visual effect you want to achieve? It is best to show it with an example table.
Best Regards,
Lionel Chen
@v-lionel-msftmy BOM table earlier was probably incomplete, really it looks more like:
Part# | Component Part# | Qty |
12345 | PartA | 2 |
PartB | 3 | |
56789 | PartA | 100 |
PartB | 3 |
So the resulting table would be something like:
YearMonth | Part 12345 | Part 56789 |
201901 | 2.5 | 125 |
201902 | 2.6 | 130 |
201903 | 3 | 150 |
201901 | 7.8 | 7.8 |
201902 | 6.3 | 6.3 |
201903 | 8.1 | 8.1 |
Show that the small price rise in component PartA has a much bigger impact on the cost of Part 56789 than it does on the cost of part 12345 due to the fact Part 56789 uses significantly more of PartA.
Make more sense?
Hi @robofski ,
How is the data in my circle calculated? Could you give me a mathematical formula?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msftSorry both entries should have been added together to get the total cost for the Parent Part so the table should have been
YearMonth | Part 12345 | Part 56789 |
201901 | 10.3 | 132.8 |
201902 | 8.9 | 136.3 |
201903 | 11.1 | 158.1 |
Basically
Parent Part Cost = SUM(Component Part Qty X Component Part Cost)
I would guess the Bill of Materials table has a date column.
You could create a 'key' with part and date and join the tables, or use the key to LOOKUPVALUE the cost that way.
@Aron_Moorethe BOM table doesn't have a date as it's static data and doesn't change, I do see where you're going though, and I guess I could add fields to that table to represent the months and then do the calculation by looking up the value for that month. Ummm....Thanks for the idea!
If that's the case, join the tables Part # to Part # and you should be able to create a measure Qty x Cost.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |