Building Effective Hierarchy Structures in Power BI

Organizational hierarchy or account mapping adds complexity to reporting. Being able to work with it with basic reporting tools like Salesforce report is impossible. For example, if your company sells to big customers with hundreds of subsidiaries, chances are that account mapping is quite important for your selling strategy. Account executives need analytics suited to account hierarchy.

For example, they need to see the billings for a specific branch. This branch can have dozens of billed subsidiaries, so the reporting needs to take these children accounts into account. With Salesforce, these requests are handled poorly due to the lack of reporting flexibility. The only option to solve these requests is to add filters like:

ParentAccount = Account A or Parent.ParentAccount = Account A…

Most companies will map their strategic accounts with Salesforce, but will struggle getting the analysis they need with SFDC reporting. In this article, I’ll show best practices concerning how to work with hierarchies. I’ll show how to work with organizational hierarchy, how to store sales quota and how to present quota achievement with Power BI. The principle explained here works for any other hierarchy structure.

Database architecture for employees, sales and quota

All you need to achieve what I’m about to show are 4 tables:

• Employees
• Sales
• Quota
• Date

Again, I’ll use AdventureWorks SQL Database.

Concerning the employee dimension, you need a column to store the employee above in the hierarchy. In our example, it’s called “ParentEmployeeKey”. Also, you need to link the dimension to the 2 fact tables (quota and sales) via the “EmployeeKey” column.

Regarding the quota fact table, the best practice is to only store the individual quota. Let’s say that a manager has a team of 4 sales. If he doesn’t have a quota for himself, you shouldn’t create a row for him in the quota table. Later, we’ll be able to roll up his team’s quota to him. This technique has several advantages. First, the manager’s quota rollup is updated automatically whenever his team’s quota changes, or when he has a new sales in his team, etc. Secondly, some sales directors have an individual quota, especially in start-up / scale-up. This architecture gives you the flexibility to assign an individual quota to managers in the hierarchy. Looking at the SQL database AdventureWorks, it seems that they opted for this design.

Visualizing Quota Achievement in Power BI

We start by creating a calculated column in the employee dimension, that will store the hierarchy path.

Hierarchy Path = PATH(DimEmployee[EmployeeKey],DimEmployee[ParentEmployeeKey])

I also created a measure to store the sum of sales amount:

Sum Sales Amount = SUM(FactResellerSales[SalesAmount])

Similarly, I store the sum of quota amount:

Sum SalesAmountQuota = SUM(FactSalesQuota[SalesAmountQuota])

Then, I created 2 measures to store the sales amount and the quota amount for the employee in the current row context, plus all the employees below in the hierarchy.

Lastly, I created 2 measures to compute the quota achievement:

Quota Achievement = DIVIDE([Sum Sales Amount],[Sum SalesAmountQuota])

Quota Achievement Hierarchy = DIVIDE([Sum Sales Amount Hierarchy],[Sum SalesAmountQuota Hierarchy])

Here’s the result. I have filtered the date table to only show 2012. We can see that Ken Sanchez, who sits at the top of the hierarchy, has a “Sum Amount Hierarchy” of 232,202,669, which is the total of the “Sum Amount” column.

Regarding quota achievement, we’re able to see each individual rate with the “Quota Achievement” measure, and each individual rollup rate with the “Quota % Hierarchy” measure.