Forum Discussion
Data Modelling Issue
Hi sarbjeet,
The repeating Actual Cost means your Finance table rows don't have a work order value at that level, so when the matrix expands to work order, it repeats the Functional Area total.
Fix in 3 steps:
Confirm the data does every Actual Cost row in Finance have a Work Order populated?
Check the relationship Dim_Order → Finance should be single direction, not bidirectional
Use a measure, not a raw column:
Actual Cost = SUM(Finance[ActualCost])
If step 1 is the problem, it's a source data issue, the fix needs to happen upstream before Power BI can split it correctly.
Hope this helps! Feel free to ask if needed. Don't forget to mark as solution and kudos. That's motivate me to keep helping.
Best regards,
Oussama (Data Consultant - Expert Fabric & Power BI)
Super User
Fix it like this:
Keep Dim_FunctionalArea as the parent.
Keep Dim_Order as the child.
Do not join fact tables to each other.
Use separate measures for each fact table.
For actual cost, decide the grain first. If it is only at functional area level, show it only at parent level.
Example:
Actual Cost = IF(ISINSCOPE(Dim_Order[WorkOrder]), BLANK(), SUM(FinanceData[Actual Cost]))
Thank you for your answer. It is working now. I am new in Power BI. Can you guide me how can I handle multiple dates. For example if I want to keep one fact table for planned and scheduled volume. The data set is same but only calculation date is different. One is calculating by basic start date and another is finish constraint date. What is best practice to keep all fact table separate or use one fact table and if any learning material which can help me to understand concept. Let me know
