1. Hierarchies and measures in Power BI
To create a hierarchy in Power BI, go to the data view. In this example, Century will be the highest level of the Year hierarchy. Right click on the dimension you want to create a hierarchy on, and select Create hierarchy. You can rename the hierarchy by double clicking it. There is also a Decade field, which can be added to the hierarchy by right clicking, and then select Add to hierarchy. The same thing applies to the Year field.
It is entirely optional, but as best practice, it's a good idea to hide the other columns.
Changing the granularity of the data by aggregating measures happens in Power Query.
Let's say for example that you only need the sum of firms and employees per year, but that the raw data takes too long to load. You can then aggregate these measures in Power Query, which will speed up the process.
First, let's duplicate Business Establishment by Age.
In the Transform menu, select Group by, and click Advanced for better control of the aggregations. The grouping will be per year, which you set on top, and the aggregations for this example are the sum of the number of firms, and the sum of the number of employees. You can rename the new columns here as well. This will modify the query and only import the fields we just specified. Let's call the table Firms_Employees by Year, with every row containing the total number of firms and employees per year.
Creating new measures in Power BI is mostly handled by using DAX. Let's for example create a new measure called Employees per Firm. You can use the DIVIDE function to divide the sum of employees by the sum of firms. The DIVIDE function has the advantage of not throwing an error when the denominator is zero. Note that this new calculated measure won't show up in this table, as it is only run when you use it in a visualization.
The last thing to demonstrate, is the use of Quick measures. Quick measures give the ability to generate some moderately complex DAX, based on an interactive template. For example, let's calculate a running total of the number of employees, per year. Select the calculation (Running total), the Base value (Sum of Employees), and the Field (Year).
So, how does this look like in a report? Let's create a Matrix, with the Year hierarchy on the rows, so that you can drill down into Century, Decade, and Year. Add Employees, Firms, and Employees per Firm as the Values. By using these collapse buttons, or these drill down buttons, you can see the individual rows per year, or aggregated by a higher level in the hierarchy.
The Running total can be visualized using a table, with the Year and Employees Running total column. You can see that there is a consistent increase over time until we get to our grand total of 1 point 3 billion employees.
Up to you now!
2. Let's practice!