Forum Discussion
Parent Child relation ship for dashboarding
Hi,
I have a 3 level parent child relation ship. and I need to summarize report at the top most hierarchy.
here is the scenario.
Effort estimation and actuals are being done on the lowest level of task i.e. WORK CR. and it has parent relationship to a mid level CR which might be an Integration CR. Finally this Integration CR may be called upon by Parent CR which could be a new requirement of a bug fix (ICR or Anomaly).
Now same Integration CR may be linked to more than one ICR or anomaly. So finally I need to divide the effort from WORK CR equally in to the ICR or Anomaly.
How could I do this calculation in Power BI DAX.
Sample data is atttached. Any help ?
eg: Look at Work CR "EA3#15808" - it has multiple entry as it is linked to 2 Parent CRs EA3#14970 , EA3#14982.
Now if I dont solve this, i am getting double booking of this task once in each parent CR.
Hi k_rahul_g ,
I think I understand your requirements and I'm assuming that the only field that will cause duplicates as described is [CR].
Based on this assumption, the following new measures for [effort budgeted] and [effort actual] should work:
_effortBudgetSplit = VAR __crCount = CALCULATE( DISTINCTCOUNT(yourTable[CR]), ALLEXCEPT(yourTable, yourTable[Work CR]) ) RETURN SUM(yourTable[effort budgeted]) / __crCount_effortActualSplit = VAR __crCount = CALCULATE( DISTINCTCOUNT(yourTable[CR]), ALLEXCEPT(yourTable, yourTable[Work CR]) ) RETURN SUM(yourTable[effort actual]) / __crCountObviously, I don't know if your screenshot shows columns or measures, or what your model/table structure is, but hopefully the above measures give you an idea of how to handle your scenario.
Pete
1 Reply
Hi k_rahul_g ,
I think I understand your requirements and I'm assuming that the only field that will cause duplicates as described is [CR].
Based on this assumption, the following new measures for [effort budgeted] and [effort actual] should work:
_effortBudgetSplit = VAR __crCount = CALCULATE( DISTINCTCOUNT(yourTable[CR]), ALLEXCEPT(yourTable, yourTable[Work CR]) ) RETURN SUM(yourTable[effort budgeted]) / __crCount_effortActualSplit = VAR __crCount = CALCULATE( DISTINCTCOUNT(yourTable[CR]), ALLEXCEPT(yourTable, yourTable[Work CR]) ) RETURN SUM(yourTable[effort actual]) / __crCountObviously, I don't know if your screenshot shows columns or measures, or what your model/table structure is, but hopefully the above measures give you an idea of how to handle your scenario.
Pete