Forum Discussion
Parent and child calculation
i have a parent and child table, i wanted to calculate the values with respect to their root parents like a family tree Parent Child Value Site1 Site2 10 Site2 Site3 20 Site3 S...
Hi Anonymous ,
You can create a measure as below to get it, please find the details in the attachment.
Output =
VAR _parent =
SELECTEDVALUE ( 'Table'[Parent] )
VAR _child =
SELECTEDVALUE ( 'Table'[Child] )
VAR _tab =
CALCULATETABLE (
VALUES ( 'Table'[Child] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Child] = _parent ||'Table'[Parent]=_parent)
)
VAR _tab2 =
CALCULATETABLE (
VALUES ( 'Table'[Parent] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Child] = _parent )
)
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Child]
IN _tab ||
'Table'[Child] IN _tab2
)
)
Best Regards
Anonymous , this works fine. Thanks..
what if i have duplicate child it gives wrong output. (on site6)
Parent Child Value Output
Site1 Site2 10 10 Site2 Site3 20 30 Site3 Site4 15 45 Site4 Site5 50 95 Site6 Site5 10 60 or if i make it reverse , so the child will always be unique.. it give a wrong output now..
Parent Child Value Column
Site2 Site1 10 45 Site3 Site2 20 85 Site4 Site3 15 65 Site5 Site4 50 60 Site5 Site6 10 60 the output should be as below.. when i make a unique child
Parent Child Value Output Parent in sequence that was added base on child name Site2 Site1 10 10+20+15+50+15=110 Site1,Site2,Site3,Site4,Site5 Site3 Site2 20 20+15+50+15=100 Site2,Site3,Site4,Site5 Site4 Site3 15 15+50+15=80 Site3,Site4,Site5 Site5 Site4 50 50+15=65 Site4,Site5 Site5 Site6 10 10+15=25 Site6,Site5 Site5 15 15 Site5 Thanks again.