I have been stuck on this particular problem for a few days and cannot seem to wrap my head around it. Currently, I have sales and target data in a table that is related to a specific payer for each transaction. I want to be able to drill down through my current hierarchy from KAM (Key Account Manager) to the specific sales agent relating to the the KAM. This relationship is defined as many-to-many (1 sales agent can have many key account managers depending on the core business field), and one key account manager is responsible for many agents.
I have created a bridge table here that contains a hierarchy for each payer. This table contains no unique columns so I cannot directly create a relationship between the hierarchy and the sales data. When I slice by sales agent or unit leader, the sales data appears to slice correctly. However, when slicing by KAM, the data does not filter sales agents or unit leader information correctly (simply repeats the same value across all agents and leaders).
KAM Sales Agent 1 10 1 11
This would be an example of the tables. KAM 1 is responsible for many sales agents (10, 11), while sales agent number 11 reports to many KAMs (1, 3). I also have a "unit leader" dimension that falls in between the sales agent and KAM. A single unit leader can have many KAMs and also many sales agents.
Could anyone help me out here and identify where my issue lies? I have attached an image of my current relationships. Thanks for your help!