Forum Discussion
Managing Cost Relationships
Hi Power Bi Users,
I have ran into a barrier learning Relationships. If anyone can point me in the right direction it would be extremely helpful.
I'll try and picture my Data for you as best i can, but simply put, Right now I have 2 sources with a specific cost column in each table. Each Table is a Metric (lets say, "Scrap" and "Freight"). Both tables have a Cost that varies in each data point displayed from, lets say, the last year.
Displaying certain relationships between both tables was easy enough, using a Material Matrix, but now my Project Leader wants a Total Cost to be able to sort in the Pareto i have created. A cost relationship cannot be created since there are no unique values with these costs.
I feel like the solution is simple enough to where i am just overlooking it.
These sources need to stay separate as much as possible for automation (rolling/updated data), optimization, and simplicity.
I apologize if my imaging is not clear enough. If you can help and need more info or attachments i will post them up.
-Fonz
3 Replies
- MFelix
Super User
Hi Anonymous,
Create a dimension table with unique ID's for the costs then add a relationship between this new table and the other two this will give you the final relationship between both of them and allow you to make calculation on those items.
Regards,
MFelix
Hi, MFelix
I think i tried this by appending a new Query from the two Cost Columns, removing duplicates, and then connecting the relationships. Is that your suggestion? How would this automate correctlhy if i was to roll or update my source data?
Or is there another way you are suggesting to create this type of relationship table? Apologies, i am still a beginner with Data alignment.
Look forward to your reply. Ty.- MFelix
Hi Anonymous,
If your new query is updated at the same time as the other two this should work when you update you source data.
Regards,
MFelix
