Forum Discussion
Data Modelling Issue
I am facing an issue in my Power BI data model and would appreciate some guidance.
Problem:
Planned volume and actual volume split correctly at work order level.
Actual cost does not split. It repeats same figure which shows at parent(Functional Area) level.
Current Model:
I have 3 separate fact table
Finance Data: It contain Actual, Budget, figures and functional area. Actual is available at work order level but not budget.
Planned Volume data: Base information is order ,opeartion, notification and functional area and more fields order level deatils and opeartion level details.
Actual Volume data-Base information is notification, order, operation and functional area and other more fields.
All three tables are connected to common Dim_FunctionalArea table.
I also created Dim_order table and connected to all fact tables using work order.
Requirements:
I need to create a matris visual with
Functional Area as parent
Work order as child
and show:
Actual cost
Budget
Planned Volume
Actual Volume
Thanks
Sarbjeet
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)
4 Replies
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)
Thank you for the answer. It is working now.
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
