Report on flattened hierarchy

I have a ragged Manager hierarchy (see attached ss) which I would like to flatten, but absolutely can’t get my head around how to do it. The Manager column is related to a dept table and onto the fact table.

I am trying to create a table visual with All Managers and YTD, Bud, LY etc regardless of postion in hierarchy. (once I have this I need to do some ranking which is pretty straight forward). But I want all managers in the hierarchy in one list regardless of hierarchy level. At the moment when I Use the manager column in the table it is only summing in accordance with the relationship. So for example Manager SS does not report any figs because he controls numerous depts. Manager DB only has figs for 1 dept whihc he controls directly, no figs for the sum of the reportees he has.

I hope I’ve explained the problem well enough.
Any help greatly appreciated.

Regards
PeteDev.xlsx (9.7 KB)

@Pete673,

I’d like to help with this, but I’m having a hard time conceptualizing the problem just based on the Excel file. Can you please post the PBIX instead, with a mockup of the final result you want to achieve?

Thanks.

  • Brian
1 Like

Thanks for posting your question @Pete673. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Thanks Brian
Pbix uploaded with table visual drilled by manager. This is OK and the structure/pattern in use in my production pbix.
What I’m trying to do is create in essence a simpler table with a column showing all managers and their result, so that all managers can be ranked. Also such a table easier to read. Appreciate the totals mean nothing but I can switch thos off once in table. See text box for desired output

Thanks
Petetemps.pbix (52.1 KB)

@Pete673,

I’m really close on this one. I modified your data model to conform to a classic star schema:

Then developed the following measure to calculate sales based on presence within the path hierarchy:

Total Sales in Hierarchy = 

VAR SelMgr = SELECTEDVALUE( ManagerList[ManagerId] )

VAR Result =
CALCULATE(
    [Sales],
    FILTER(
        ALL( ManagerList ),
        PATHCONTAINS( ManagerList[ManagerHierarchy], SelMgr   )
    )
)

RETURN 
Result

It’s calculating correctly per your mockup for every manager except RH and SS. Before I do a deep dive into why it’s producing different values for just these two, can you please double check and confirm that the totals in your mockup for RH and SS are indeed correct? It’s just very strange that these two are not returning the mockup results, when others at the same hierarchy level are returning the anticipated values.

image

Thanks very much.

– Brian

1 Like

All works Brian. Fantastic. I can now move onto sorting out some of the managers that have dotted reporting lines via your solution and a switch statement with an alternative CALCULATE.

And yes I had the numbers wrong in the mock up. Cool too that I was able to get my production model into a star schema for manager table. I do still have to do that with a couple of tables, but hey what I got works and is quick.

Thanks again for your help. I’ll have deeper dive on PATHCONTAINS soon.

Pete

@Pete673,

Excellent – glad to hear that got you what you needed.

You’ve done a really nice job of setting up the hierarchy, which is the critical part. In addition to PATHCONTAINS, I’d recommend you check out the full suite of Path-related functions in the Enterprise DNA Knowledgebase. These give you a really powerful set of tools to traverse your hierarchies in multiple ways, and allow you to do some really interesting analyses.

Best of luck with your project.

  • Brian