Hi
Traying to nail the problem on hierarchy i.e grouping and adding of child quantity in respective parent with Hierarchy of maximum level 4. Also want to connect result in power view ( Not using power BI ) so need to remove blanks in view
Hope for results
Hello kapil D and Welcome to Excel
This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
The helper column for level4 (M) is populated using: =IF(F4<>"",$G4,"")
The helper columns for levels 1:3 (J:L) are populated using: =IF(AND(C4<>"",$G4=""),SUMIFS(K$4:K$14,D$4:D$14,"<>"),IF(C4<>"",$G4,""))
The Result column (H) is populated using: =SUM(J4:M4)
I have never used Power View, so I can not help there.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Really appreciable efforts for fixing issue . satisfiedlots of time saving
I will build logic as follows
For function IF(AND(C4<>"",$G4=""),SUMIFS(K$4:K$14,D$4:D$14,"<>"),IF(C4<>"",$G4,""))
Logic considered as IF ( Check both condition of C4 is Non blank ,and G4 is blank , true= siumifs ( Sum of column K (Child range in level 2 ), with Criteria of Column D ( Level 2 ) ,with criteria anything ( "<>")) ) ) , for False ( if C4 is Non blank, True = G4, False =Blank )
Hope Understood correct for Sumifs Crteria
Checking result in Power view
Yes, your interpretation of the logic seems to be correct.
Please show us the file with Power View if possible, as it may help those whom want similar results in the future.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks