Step #1 : Import your data
Start by importing your dataset in Power BI. In our example we will use the Get Data function to hire an Excel file.
Here is an extract of our Hiring Campaign table showing details on an imaginary campaign for Business Intelligence Roles in a consultancy.
Our goal will be to create a report showing a simple analysis of the hiring activities. We will specifically show how to rank grouped data according to a calculated column (or measure) value.
Step #2: Aggregate your columns
As we have seen, there are several ways to group table data in Power BI. In this example, we will create a calculated table in Power BI by summarizing our data set.
To group by our data in Power BI proceed as following:
- From the Power BI left hand side bar menu, hit the Table View.
- From the Ribbon, hit the New Table button.
- Then calculate the Number of Interviews and Average number of hires for each Role. Use the following DAX code:
Hiring by Role =
SUMMARIZE(
'Hiring Campaign','Hiring Campaign'[Role],
" # of Interviews",Sum('Hiring Campaign'[Interviews])
,"Avg # of Hired Employees",AVERAGE('Hiring Campaign'[Hired])
)- Commit your DAX expression by clicking the V icon at the right of your formula row.
Step #3: Aggregate and Rank your table
To rank the dataset according to a column proceed as following:
- Still in the Table View, Hit the New Column button.
- Create a calculated column named Interview Ranking that will rank the Number of Interviews column in a descending order using DAX, and specifically the RANKX iterator:
Interview Ranking = RANKX ('Hiring by Role','Hiring by Role'[ # of Interviews],,DESC)- Similarly, create a second calculated column in your table to rank the number of hired employees in the campaign.
Hired Employees Ranking = RANkX('Hiring by Role','Hiring by Role'[Avg # of Hired Employees],,DESC)Your calculated table will contain the following information:
