We want to calculate the percentage contribution of headcount across departments for the overall data.

Our Data Model contains three tables Department, Gender and Emp_data.

The dimension tables are Department and Gender. The fact table is the Emp_data table.

• The Department table has a list of all the available departments in our dataset
• The Gender table has a list of all the available gender categories in our dataset.
• The Emp_data table has details for all our active employees. We have details like the department, gender, grade, designation, joining date , country and many more data.

The Department table and the Gender table are connected to our Emp_Data table in a one to many relationship.

What is Percent of Grand Total

The below table gives us the count of employees across Department by Gender.

We have a total of 32977 employees and the breakdown of employees by Department

For Percent of Grand Total, we need to find out the % values of employees across Departments. The sum of all the values across entire matrix needs to be 100%.

If we individually look at the Department% and sum the %values of Gender in each Department. It would be equal to the sum of the Department%

Our result must be as under:

What the above tables states is that:

• Customer Service accounts for 4.5% of employees,
  • Of which females account for 2.8%
  • Males account for 1.6%
  • Undisclosed accounts for 0.1%

If we sum the individual gender% for each department, it will sum up to the %value for that department.

To achieve the below we need to calculate three measures

Calculate the Headcount in our dataset. Create a new measure. Name it as Headcount.

Next, we need to calculate the Grand total. So we will create a new measure. Name it as Grand Total.

As we want the overall grand total. We remove filters on the Department and also on the Gender.

Finally, we need to calculate the Percent of grand total by dividing the Headcount by the Grand Total.

So create a new measure. Name it as Percent of Grand Total.Format it as %.

Add a matrix visual to your report.

In rows add Department (from Department table) and below it in rows add Gender ( from Gender table). In values add Percent of Grand Total.

The resultant matrix is our Percent of Grand Total.

To view a video tutorial. Refer below.