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.