Forum Discussion

Soc3's avatar
Soc3
Helper I
1 year ago
Solved

Org structure drill down - previous level continuing to show

I built a org structure using: 

Path = PATH('WORKFORCE'[PRSN_ID], 'WORKFORCE'[ParentPath])
 
Org level 2 = LOOKUPVALUE(WORKFORCE[Traveler Details], WORKFORCE[PRSN_ID], PATHITEM(WORKFORCE[Path], 2, TEXT))
 
And repeated this logic with a measure for each org level 3, 4, 5, etc. 
 
I have a stacked bar chart that allows drill down to each org level and shows a total spend per org level. Org leaders have individual spend that is included in the drill down.
 
The problem is, when I drill down from org 2 to org 3, the dollar amount for the specific org 2 leader appears in org level 3 as "blank". There is no org level 3 name in this case but I don't want to filter out blank values as the spend still occurred but it should only show at org level 2.
 
Why is my org level 2 data appearing on org level 3 and how do I prevent this? 
 
Thanks!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Soc3 ,
    Based on your question , I think you want to make it so that when organization tier 2 drills down to tier 3, the amount in tier 2 belongs only to tier 2 and should not appear in tier 3, right?

    If this is the case, it is because each of the organizational structure we derived has a blank value, and each blank value has a corresponding send, then in the stacked graph will be automatically aggregated together by default blank, if we want to eliminate this effect, we have to do is to re-create a new aggregation table, will be screened out each blank paper, to ensure that our stacked graph visual object is free of blanks, thus ensuring the accuracy of the answer.

    Level 2 Table = SELECTCOLUMNS(
    FILTER(
        'WORKFORCE',
        NOT(ISBLANK('WORKFORCE'[Org Level 3])) 
    ),
    "Traveler Details", 'WORKFORCE'[Traveler Details],  
    "Org Level 3", 'WORKFORCE'[Org Level 3]             
)

    If you have further questions you can check out my pbix file, I hope this helps and I would be honored if I could solve your problem!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     



1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Soc3 ,
    Based on your question , I think you want to make it so that when organization tier 2 drills down to tier 3, the amount in tier 2 belongs only to tier 2 and should not appear in tier 3, right?

    If this is the case, it is because each of the organizational structure we derived has a blank value, and each blank value has a corresponding send, then in the stacked graph will be automatically aggregated together by default blank, if we want to eliminate this effect, we have to do is to re-create a new aggregation table, will be screened out each blank paper, to ensure that our stacked graph visual object is free of blanks, thus ensuring the accuracy of the answer.

    Level 2 Table = SELECTCOLUMNS(
    FILTER(
        'WORKFORCE',
        NOT(ISBLANK('WORKFORCE'[Org Level 3])) 
    ),
    "Traveler Details", 'WORKFORCE'[Traveler Details],  
    "Org Level 3", 'WORKFORCE'[Org Level 3]             
)

    If you have further questions you can check out my pbix file, I hope this helps and I would be honored if I could solve your problem!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.