Archives

Compare Selected Categories against each other

May18

We have two slicers for Categories. We select one Category from Slicer 1. The concerned category gets excluded from the other Category from Slicer 2.

We can select one Category from slicer 1 and other Category from slicer 2. This will allow us to compare two different categories against each other.

Our Data Model contains three tables Orders, Products and Order_Details.

The dimension tables are Orders, Products. The fact table is the Order_Details table.

  • The Orders table has details of all the orders in our dataset
  • The Products table has details of all the products in our dataset
  • The Order_Details has line item details of the orders.
  • The Calendar Table is our date table
  • The Categories has details of all the categories in our dataset

The Orders, Products table are connected to our Order_Details table in a one to many relationship.

The Calendar table is connected with our Orders Table in a one to many relationship.

The Category Table is connected with our Products Table in a one to many relationship.

Create a measure called Total Sales.

Create two tables called as Category1 and Category2.

Add two slicers and add categoryname( from Category1 table) in one slicer . In the other slicer add categoryname( from Category2 table)

This will allow to compare if a selection in the first slicer is also selected in the second slicer.

Now to calculate the sales of the selected category , create a measure called Check Selections.

As our Category 1 and Category 2 table are disconnected tables. We create a measure called Compare Categories Sales to check if either of our selection [categoryname] from Category 1 and Category 2 table is same as the [categoryname] Category from our Category table.

Add a column chart . In X Axis add Short Month Name and Y Axis add Compare Categories Sales.

 

However, if you do not select anything from both slicers , our chart will be blank.

To solve this, we create a new measure called Final Compare Category Sales.

Add a column chart . In X Axis add Short Month Name and Y Axis add Final Compare Category Sales.

The final chart after formatting colours is as below.

 

 

 

Deselect Items basis Slicer Selection

Apr18

Our Chart has categories and their Total Sales. We have a slicer which has the Category Name. Basis the selection from our slicers, the concerned category will be deselected from our chart.

This is different than the usual nature of slicers where selecting a category means that category will show in the Chart.

Our Data Model contains three tables Orders, Products and Order_Details.

The dimension tables are Orders, Products. The fact table is the Order_Details table.

  • The Orders table has details of all the orders in our dataset
  • The Products table has details of all the products in our dataset
  • The Order_Details has line item details of the orders.
  • The Calendar Table is our date table
  • The Categories has details of all the categories in our dataset

The Orders, Products table are connected to our Order_Details table in a one to many relationship.

The Calendar table is connected with our Orders Table in a one to many relationship.

The Category Table is connected with our Products Table in a one to many relationship.

Create a measure called Total Sales.

Create a table called New Category.

Next create a measure called Deselect Slicers.

Create a Column Chart. In X Axis add categoryname ( from Category Table) and Y Axis as Total Sales

Add the measure Deselect Slicers in the Chart’s Visual Filter.

Add a Slicer. In the field add categoryname from the New Categorytable.

Now when you select categories from the slicer, the particular category is excluded from the Chart.

Highlight Months with Above/Below Avg Hires

Mar5

We want to highlight the Months in a Year with the above and below average total hires.

Our Data Model contains four tables Calendar,Department, Grade_Level,Country and Hires_data.

The dimension tables are Calendar, Department, Grade_Level, Country. The fact table is the Hires _data table.

  • The Calendar table is the date table in our dataset.
  • The Grade_Level table has a list of all the available grade levels in our dataset.
  • The Department table has a list of all the available departments in our dataset.
  • The Country table has a list of all the available countries in our dataset.
  • The Hires_data table has details for all our newly hired employees. We have details like the department, gender, grade, designation, joining date , country and many more data.

The dimension table are connected to our Hires_Data table in a one to many relationship.

We want to highlight the months with Above and Below Average Hires in a year.

First, we create a measure called Total Hires.

Next, we create the measure Average Monthly Hires.

To calculate the Monthly Hires across All Selected years . Create a new measure Monthly Averages AllSelected.

To highlight the Above Average/Below Average Hires we need to compare the Total Hires with Above Average/Below Average Hires.

Create a new measure called Check Above Below Averages

Matrix Highlighting

Add a Table visual to the report. In columns add Month Name and below it add the measure Total Hires. Now Add measure Monthly Averages AllSelected below it.

To highlight the values . Remove the measure Monthly Averages AllSelected  from the table.

Go to visual –> Cell Elements –> Series (Total Hires) –> Background Colour.

In the conditional formatting , add the measure Check Above Below Averages

Column Chart Highlighting

Add a column visual. In X Axis add Month Name. In Y Axis add Total Hires.

To highlight the values. Go to visual –> Columns –> Colours

In the conditional formatting, add the measure Check Above Below Averages.

Line Chart Highlighting

The Line chart does not have the conditional formatting for markers / lines. To achieve the conditional formatting, we first create the column chart with conditional formatting and then convert it to Line Chart.

Change the colour of the line and the markers size .

 

Highlight Months with Max and Min Hires

Feb14

We want to highlight the Months in a Year with the maximum sales and minimum sales.

Our Data Model contains four tables Calendar,Department, Grade_Level,Country and Hires_data.

The dimension tables are Calendar, Department, Grade_Level, Country. The fact table is the Hires _data table.

  • The Calendar table is the date table in our dataset.
  • The Grade_Level table has a list of all the available grade levels in our dataset.
  • The Department table has a list of all the available departments in our dataset.
  • The Country table has a list of all the available countries in our dataset.
  • The Hires_data table has details for all our newly hired employees. We have details like the department, gender, grade, designation, joining date , country and many more data.

The dimension table are connected to our Hires_Data table in a one to many relationship.

We want to highlight the months with Max and Min Hires in a year.

First, we create a measure called Total Hires.

Next, we create measures for Max and Min Hires.

To highlight the Max/Min Hires we need to compare the Total Hires with Max and Min Hires. Create a measure Highlight Hires Colours.

Matrix Highlighting

Add a Table visual to the report. In columns add Month Name and below it add the measure Total Hires.

To highlight the values . Go to visual –> Cell Elements –> Series (Total Hires) –> Background Colour.

In the conditional formatting , add the measure Highlight Colour

Column Chart Highlighting

Add a column visual. In X Axis add Month Name. In Y Axis add Total Hires.

To highlight the values. Go to visual –> Columns –> Colours.

In the conditional formatting, add the measure Highlight Colour.

Line Chart Highlighting

The Line chart does not have the conditional formatting for markers / lines. To achieve the conditional formatting, we first create the column chart with conditional formatting and then convert it to Line Chart.

Change the colour of the line and the markers size .

 

 

 

Rank Employees with Highest Salary using TopN

Feb7
TOPN PowerBI

We want to calculate the Top Number of Salaried employees per country. The Top Number will be basis our user selection.

Our Data Model contains two tables Country and Emp_Data.

  • The Country table has a list of all the available countries 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 dimension tables are Country. The fact table is the Emp_Data table.

The Country Table is connected to our Emp_Data table in a one to many relationship.

TOP Salaries

We want to rank the employees who have the highest salaries.

So first create a new measure and Name it as Total Salary.

To find the Top the Values, we need to specify the number we want. That is the find the Top 3 values  or the Top 5 Values. So we create a Numeric Parameter. This numeric parameter will help us determine this value.

Go to Modelling – Numeric Range

Create a parameter. Name it TopNRequired. The Minimum Value is 1 and Maximum Value is 10.

Once we create a NumericRange. We get a Slider on the Report.

To find the TopValues. We create a new measure . Name it TopSalary.

We use the numeric parameter in our TOPN function to determine how many TOPN values we require.

Next add the Table Visual. In Columns add Country ( from the country table), Full_Name (from the Emp_Data table) and the measures Top Salary.

This will give us the Country , the employee names and their salary.  We will get the TOPN employees by salary per Country.

So if we select 6 in the Numeric Slider. It will give the Top 6 salaried employees per country.

If you move the slider across, the number changes. And you will get the Top values as per the slider numeric parameter.

Now if you look at the grand total it is 1238639. However it is not the value you will get if you sum to above values.

This value of 1238639 is the overall Top 6 salary in our dataset (irrespective of any filters). As the Grand total is not having any filter associated with it , we get this value.

What we want is the grand total to show the sum of the above values.

To achieve this, we create a new measure. Name it as Top Salary Sum.

Now add this measure to our Table visual.

If you look at the Grand Total , it will give us the sum of the above values.

The Top Salary and the Top Salary Sum measure gives the same value for all the rows. Except on the Grand Total.

  • The Top Salary gives the overall Top 6 Salary.
  • Whereas the Top Salary Sum gives us the sum of the top 6 salary values as the Grand Total

To view a Video Tutorial, Refer Below:

Rank Products with Highest Sales using RankX

Feb5

We want to rank the products by their Total Sales . The Ranking number will be as per user selection.

Our Data Model contains three tables Orders, Products and Order_Details.

The dimension tables are Orders, Products. The fact table is the Order_Details table.

  • The Orders table has a details of all the orders in our dataset
  • The Products table has a details of all the products in our dataset
  • The Order_Details has a line item details of the orders.

The Orders, Products table are connected to our Order_Details table in a one to many relationship.

RANK Products by Sales.

We want to rank the products by Sales. Create a new measure and Name it as Total Sales.

To Rank the Values, we need to specify the number we want. That is the Rank the Top 3 values  or Rank the Top 5 Values. So we create a Numeric Parameter. This numeric parameter will help us determine this value.

Go to Modelling – Numeric Range

Create a parameter. Name it RankValue. The Minimum Value is 1 and Maximum Value is 10.

Once we create a NumericRange. We get a Slider on the Report.

To create the Rank. We create a new measure . Name it Rank Top.

Next add the matrix visual. In rows add productname (from Products Table) and in values add the RankTop Measure. This will give us the ProductName and the Sales of the Product as per the Numeric Range given in the Slider. That is ranked values.

So if we select 6 in the Numeric Slider. It will give the products with the Top 6 sales.

If you move the slider across, the number changes. And you will get the Top values as per the slider numeric parameter.

Now if you look at the grand total it is 1354459. However it is not the value you will get if you sum to above values.

This value of 1354459 is the overall Total Sales in our dataset. As the Grand total is not having any filter associated with it , we get this value.

What we want is the grand total to show the sum of the above values.

To achieve this, we create a new measure. Name it as Rank Sum.

Now add this measure to our matrix visual. If you look at the Grand Total , it will give us the sum of the above values.

The Rank Top and the Rank Sum measure gives the same value for all the rows. Except on the Grand Total.

The Rank Top gives the overall sales as the Grand Total.

Whereas the Rank Sum gives us the sum of the top values as the Grand Total.

To view the video tutorial. Refer below.

 

Percent of Grand Total

Feb4

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.

 

Percent of Column Total

Feb3

We want to calculate the percentage contribution of the columns. In our example the we want to calculate the percent contribution of gender across Department.

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 Column Total

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

E.g Gender : Female

We have a total  of 4463 female employees and the breakdown of female employees by Department

For percent of column total we need to find out the % values of female employees across Departments. The sum of all the values needs to be 100%

Our result must be as under:

What the above tables states is that in Female Gender.

  • Customer Service accounts for 21% female employees,
  • Finance & Accounts accounts for 13% female employees,
  • Human Resource accounts for 6% female employees
  • And so on……….

The Total is 100% for females.

Similarly for Male employees , we get the %breakdown for male employees by Department.

And similar for Undisclosed Gender.

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 Column total. So we will create a new measure. Name it as Column Total.

As we want the % across Gender, we need to remove the filters across the Department.

Finally, we need to calculate the % of Percent of column total by dividing the headcount by the column total.

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

Add a matrix visual to your report.

In rows add Department (from Department table). In columns add Gender (from Gender Table). In values add Percent of Column Total

The resultant matrix is our percent of parent column total.

To view a video tutorial. Refer below.

 

Percent of Parent Row Total

Feb2

We want to calculate the percentage contribution of the child rows to the parent row. In our example the Department are the child rows and each has a parent which is the Gender.

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 PARENT Row Total

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

E.g Gender : Female

We have a total  of 4463 female employees and the breakdown of female employees by Department

For percent of parent row total we need to find out the % values of female employees across Departments. The sum of all the values needs to be 100%

Our result must be as under:

What the above tables states is that in Female Gender.

  • Customer Service accounts for 21% female employees,
  • Finance & Accounts accounts for 13% female employees,
  • Human Resource accounts for 6% female employees and so on……….

The Total is 100% for females.

Similarly for Male employees , we get the %breakdown for male employees by Department.

And similar for Undisclosed Gender.

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 Parent Row Total. So we will create a new measure. Name it as Row Total.

As we want the % across Gender, we need to remove the filters across the Department.

Finally, we need to calculate the Percent of Parent Row Total by dividing the headcount by the percent parent row total.

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

Add a matrix visual to your report.

In rows add Gender (from the Gender Table). Below it in rows also add Department (from the Department Table). In values add Percent of Row Total

The resultant matrix is our percent of parent row total.

To view a video tutorial. Refer below.

 

Percent of Row Total

Feb2

To calculate the percent of Row Total we need to find out the percentage split of the column categories against the rows. In our example we need to find out the gender% split across the various departments.

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 Row Total

The below table gives us the count of employees by department across gender.

E.g Department Customer Service

The female headcount is 930, the male headcount is 530 and the undisclosed headcount is 39.

For percent of row total we need to find out the % values of female, male and undisclosed for each department. The sum of all the values need to be 100%

Our result must be as under:

What the above tables states is that in Customer Service Female accounts for 62% employees, Male accounts for 35% employees and Undisclosed accounts for 3% employees. The Total is 100%

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 Row Total. So we will create a new measure. Name it as Row Total.

As we want the % across rows we need to remove the filters across the columns. In our example , the columns are gender.

Finally, we need to calculate the Percent of Row Total by dividing the headcount by the row total.

Create a new measure. Name it as Percent of Row Total.

Format it as %.

Add a matrix visual to your report.

In rows add Department (from the Department Table), in columns add Gender (from the Gender Table) and in values add Percent of Row Total.

The result you see in the matrix is the Percent of Row Total.

 

To view the video tutorial , refer below